FOR JSON句を使ってクエリの結果をJSON形式にするサンプルクエリを書いて試してみました。

試してみたのはこのあたり。

まずはテスト用のデータです。

drop table if exists #Temp;

select *
into #Temp
from (values
    (1, 'a', null)
) as Temp(X, Y, Z);

select *
from #Temp;
/*
X           Y    Z
----------- ---- -----------
1           a    NULL
*/

FOR JSON句を使ってクエリを実行すると、JSON文字列を1レコードの結果セットとして取得できます。JSON文字列は配列です。 また、実際の結果セットにはカラム名が入り、JSON文字列も余分な空白がない状態で出力されます。

以降ではカラム名を省略して、すべて見やすいように整形したJSON文字列を表示しています。

select *
from #Temp
for json auto;
/*
[
    {
        "X": 1,
        "Y": "a"
    }
]
*/

AUTOモードとPATHモードの違い

FOR JSON句ではAUTOモードかPATHモードを指定します。

AUTOモードでは、カラム名が出力されるプロパティ名になります。.を含むカラム名でも入れ子になったオブジェクトにはなりません。 一方、PATHモードでは、.区切りのカラム名を指定すると入れ子になったオブジェクトとして出力できます。

結果の違いを確認してみましょう。

-- AUTOモード
select
    X,
    Y as 'detail.y'
from #Temp
for json auto;
/*
[
    {
        "X": 1,
        "detail.y": "a"
    }
]
*/

-- PATHモード
-- 入れ子になったオブジェクトとして出力される
select
    X,
    Y as 'detail.y'
from #Temp
for json path;
/*
[
    {
        "X": 1,
        "detail": {
            "y": "a"
        }
    }
]
*/

ROOTオプション

ROOTオプションを指定すると、引数に指定したプロパティ名を持つオブジェクトとして出力できます。

-- ROOTオプションあり
-- "items"プロパティを持つオブジェクトとして出力される
select
    X as x,
    Y as y
from #Temp
for json path, root('items');
/*
{
    "items": [
        {
            "x": 1,
            "y": "a"
        }
    ]
}
*/

INCLUDE_NULL_VALUESオプション

オプション名の通りですね。NULLを出力したい場合に指定するオプションです。

-- INCLUDE_NULL_VALUESオプションあり
select Z as z
from #Temp
for json path, include_null_values;
/*
[
    {
        "z": null
    }
]
*/

WITHOUT_ARRAY_WRAPPERオプション

配列として出力しないオプションです。 配列内の要素が複数でもカンマ区切りとして出力されてJSONとして正しくない気がするので、配列の要素が1つのときに使うのかも？

select X as x
from #Temp
for json path, without_array_wrapper;
/*
{
    "x": 1
}
*/

ROOTオプションとWITHOUT_ARRAY_WRAPPERオプションどちらも指定するとエラーになります。

select *
from #Temp
for json path, root('items'), without_array_wrapper;
-- エラー
-- ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON. Remove one of these options.