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 テーブルがあります。
このようなクエリーの結果を、JSON 文字列に変換してみます。
※ サンプルデータを生成するスクリプトは こちら にありますが、同じ名前のテーブルが既に存在している場合、削除されてしまうのでご注意ください。
FOR JSON AUTO で同じクエリーを自動的に JSON 文字列に変換した時は、JOIN した部分でオブジェクトがネストするような形になりましたが、今回は FOR JSON PATH をつかって、JSON した部分も同じ階層のプロパティになるように出力してみます。
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;
[ 実行結果 ]
得られた 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;
[ 実行結果 ]
得られた 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 でプロパティーのキーとなる名前を指定し、ドットで区切ることによって、ネストした出力も簡単に書式設定することができます。