FOR JSON を使用してクエリ結果を JSON として書式設定する

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics (サーバーレスの SQL プールのみ)

FOR JSON 句を SELECT ステートメントに追加して、クエリ結果を JSON として書式設定するか、SQL Server から JSON としてデータをエクスポートします。 FOR JSON 句を使用して、JSON 出力の書式設定をアプリから SQL Server に委任することによって、クライアント アプリケーションを簡素化します。

Note

Azure Data Studio は、この記事に示されているように JSON の結果を自動的に書式設定するので、JSON クエリ用に推奨されるクエリ エディターです。 SQL Server Management Studio では、書式設定されていない文字列が表示されます。

クエリ結果を書式設定する

FOR JSON 句を使用すると、JSON 出力の構造を明示的に指定したり、SELECT ステートメントの構造によって出力を決定したりできます。

• JSON 出力の形式を完全に制御するには、FOR JSON PATH を使用します。 ラッパー オブジェクトを作成して、複雑なプロパティを入れ子にすることができます。

• SELECT ステートメントの構造に基づいて JSON 出力を自動的に書式設定するには、FOR JSON AUTO を使用します。

FOR JSON 句とその出力を使用した SELECT ステートメントの例を次に示します。

FOR JSON PATH

FOR JSON PATH で出力を制御する

PATH モードではドット構文を使用できます。たとえば、ネストした出力を書式設定するには、Item.Price と指定します。

FOR JSON 句で PATH モードを使用するサンプル クエリを以下に示します。 次の例では、ROOT オプションを使用して名前付きのルート要素を指定しています。

FOR JSON PATH に関する詳細情報

詳細と例については、「PATH モードで入れ子になった JSON 出力を書式設定する (SQL Server)」を参照してください。

構文と使用法については、「FOR 句 (Transact-SQL)をご覧ください。

FOR JSON AUTO

SELECT ステートメントで FOR JSON AUTO を使用して出力を制御する

AUTO モードでは、SELECT ステートメントの構造によって JSON 出力の形式が決まります。

既定では、NULL 値は出力に含まれません。 INCLUDE_NULL_VALUES を使用して、この動作を変更できます。

FOR JSON 句で AUTO モードを使用するサンプル クエリを以下に示します。

SELECT name, surname
FROM emp
FOR JSON AUTO;

また、返された JSON を以下に示します。

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

JOIN と NULL を使用した例

以下の SELECT...FOR JSON AUTO の例には、結合されたテーブルのデータ間に 1 対多の関係がある場合に JSON の結果がどのように表示されるかが含まれています。

返された JSON に null 値が存在しないことも示されています。 ただし、この既定の動作は、FOR 句で INCLUDE_NULL_VALUES キーワードを使用することでオーバーライドできます。

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

次に示すのは、上記の SELECT により出力される JSON です。

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

FOR JSON AUTO に関する詳細情報

詳細と例については、「AUTO モードで自動的に JSON 出力を書式設定する (SQL Server)」を参照してください。

構文と使用法については、「FOR 句 (Transact-SQL)をご覧ください。

他の JSON 出力オプションを制御する

次の追加オプションを使用して、FOR JSON 句の出力を制御します。

• ROOT

  JSON 出力に最上位の単一要素を追加するには、ROOT オプションを指定します。 このオプションを指定しないと、JSON 出力にはルート要素がありません。 詳細については、「ROOT オプションを使用して JSON 出力にルート ノードを追加する (SQL Server)をご覧ください。

• INCLUDE_NULL_VALUES

  JSON 出力に null 値を含めるには、INCLUDE_NULL_VALUES オプションを指定します。 このオプションを指定しないと、出力にはクエリ結果の NULL 値に対する JSON プロパティは含まれません。 詳細については、「JSON に Null 値を含める - INCLUDE_NULL_VALUES オプション」をご覧ください。

• WITHOUT_ARRAY_WRAPPER

  既定で FOR JSON 句の JSON 出力を囲む角かっこを削除するには、WITHOUT_ARRAY_WRAPPER オプションを指定します。 このオプションを使用して、1 行の結果からの出力として単一の JSON オブジェクトを生成します。 このオプションを指定しない場合、JSON 出力は配列としてフォーマットされるため、出力は角かっこで囲まれます。 詳細については、「WITHOUT_ARRAY_WRAPPER オプションを使用して JSON から角かっこを削除する」を参照してください。

FOR JSON 句の出力

FOR JSON 句の出力には、次の特徴があります。

1. 結果セットには 1 つの列が含まれます。

   • 小さな結果セットには 1 つの行を含めることができます。
   • 大きな結果セットでは、長い JSON 文字列が複数行に分割されます。

     • 出力設定が結果をグリッドに表示の場合、SQL Server Management Studio (SSMS) は既定で結果を単一行に連結します。 SSMS のステータス バーに、実際の行数が表示されます。

     • 他のクライアント アプリケーションでは、複数の行の内容を連結することによって長い結果を単一の有効な JSON 文字列に結合し直すにはコードが必要になることがあります。 C# アプリケーションでのこのコードの例については、「C# クライアント アプリで FOR JSON 出力を使用する」をご覧ください。

       

2. 結果は JSON オブジェクトの配列として書式設定されます。

   • JSON 配列の要素の数は、(FOR JSON 句が適用される前の) SELECT ステートメントの結果の行数と同じです。

   • (FOR JSON 句が適用される前の) SELECT ステートメントの結果の各行は、配列内の個別の JSON オブジェクトになります。

   • (FOR JSON 句が適用される前の) SELECT ステートメントの結果の各列は、JSON オブジェクトのプロパティになります。

3. 列の名前とその値は、JSON の構文に従ってエスケープされます。 詳細については、「FOR JSON が特殊文字と制御文字をエスケープする仕組み (SQL Server)」をご覧ください。

例

FOR JSON 句による JSON 出力の書式設定の例を次に示します。

Query results

A	B	貸方	D
10	11	12	x
20	21	22	年
30	31	32	Z

JSON 出力

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

関連するコンテンツ

• FOR JSON が SQL Server データ型を JSON データ型に変換する方法 (SQL Server)
• FOR JSON での特殊文字のエスケープと制御文字 (SQL Server)
• NoSQL とリレーショナル環境間の架け橋としての JSON
• SELECT - FOR 句 (Transact-SQL)
• SQL Server およびクライアント アプリ (SQL Server) での FOR JSON 出力の使用