SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > T-SQL でクエリの結果を JSON 文字列に変換する - FOR JSON PATH

T-SQL でクエリの結果を JSON 文字列に変換する - FOR JSON PATH

T-SQL でクエリの結果を JSON 文字列へ

SQL Server の 2016 以降では、組み込みの JSON ファンクションが提供されて、簡単に T-SQL でクエリの結果を JSON 文字列に変換したり、JSON 文字列を解析して、値を取得したりクエリーを書いたりできるようになりました。

前回は 「 FOR JSON AUTO 」 で FOR JSON AUTO を使って、T-SQL でクエリの結果を自動的に JSON 文字列に変換しました。

今回は FOR JSON PATH を使ってクエリの結果を制御して JSON 文字列に変換してみます。


FOR JSON PATH

次のような Student テーブルと TestResult テーブルがあります。

T-SQL でクエリの結果を JSON 文字列に変換する 1

このようなクエリーの結果を、JSON 文字列に変換してみます。

T-SQL でクエリの結果を JSON 文字列に変換する 2

※ サンプルデータを生成するスクリプトは こちら にありますが、同じ名前のテーブルが既に存在している場合、削除されてしまうのでご注意ください。


FOR JSON AUTO で同じクエリーを自動的に JSON 文字列に変換した時は、JOIN した部分でオブジェクトがネストするような形になりましたが、今回は FOR JSON PATH をつかって、JSON した部分も同じ階層のプロパティになるように出力してみます。

FOR JSON AUTO の結果はこちらです


FOR JSON PATH をつかって、JSON 形式に変換された文字列を取得するクエリーは以下の通りです。

SELECT  TOP 2
        S.StudentID AS 'StudentID',
        S.FirstName AS 'FirstName',
        S.LastName  AS 'LastName',
        TR.TestResultCount AS 'TestResultCount',
        TR.ScoreAverage AS 'ScoreAverage'
FROM    Student AS S
            INNER JOIN 
                (SELECT     StudentID,
                            COUNT(Score) AS TestResultCount,
                            AVG(CAST(Score AS DECIMAL)) AS ScoreAverage
                 FROM       TestResult
                 GROUP BY   StudentID) AS TR
            ON S.StudentID = TR.StudentID
ORDER BY TR.ScoreAverage DESC
FOR JSON PATH;

[ 実行結果 ]
T-SQL でクエリの結果を JSON 文字列に変換する 1

得られた JSON の文字列を読みやすいように改行すると次のようになります。

[
   {
      "StudentID":3,
      "FirstName":"Yuko",
      "LastName":"Suzuki",
      "TestResultCount":1,
      "ScoreAverage":92.000000
   },
   {
      "StudentID":4,
      "FirstName":"Takao",
      "LastName":"Sato",
      "TestResultCount":2,
      "ScoreAverage":89.500000
   }
]

TestResultCount と ScoreAverage が他の学生情報を同じ階層のプロパティとしてフォーマットされていますね。


次は、FirstName と LastName が StudentInfo というネストしたオブジェクトのプロパティになるようにしてみます。

SELECT  TOP 2
        S.StudentID AS 'StudentID',
        S.FirstName AS 'StudentInfo.FirstName',
        S.LastName  AS 'StudentInfo.LastName',
        TR.TestResultCount AS 'TestResultCount',
        TR.ScoreAverage AS 'ScoreAverage'
FROM    Student AS S
            INNER JOIN 
                (SELECT     StudentID,
                            COUNT(Score) AS TestResultCount,
                            AVG(CAST(Score AS DECIMAL)) AS ScoreAverage
                 FROM       TestResult
                 GROUP BY   StudentID) AS TR
            ON S.StudentID = TR.StudentID
ORDER BY TR.ScoreAverage DESC
FOR JSON PATH;

[ 実行結果 ]
T-SQL でクエリの結果を JSON 文字列に変換する 2

得られた JSON の文字列を読みやすいように改行すると次のようになります。

[
   {
      "StudentID":3,
      "StudentInfo":{
         "FirstName":"Yuko",
         "LastName":"Suzuki"
      },
      "TestResultCount":1,
      "ScoreAverage":92.000000
   },
   {
      "StudentID":4,
      "StudentInfo":{
         "FirstName":"Takao",
         "LastName":"Sato"
      },
      "TestResultCount":2,
      "ScoreAverage":89.500000
   }
]

FirstName と LastName が StudentInfo というネストしたオブジェクトのプロパティとしてフォーマットされました。


このように、FOR JSON PATH を使うと、カラム名に続いて AS でプロパティーのキーとなる名前を指定し、ドットで区切ることによって、ネストした出力も簡単に書式設定することができます。

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > T-SQL でクエリの結果を JSON 文字列に変換する - FOR JSON PATH