T-SQL でクエリの結果を JSON 文字列に変換する - FOR JSON AUTO
T-SQL でクエリの結果を JSON 文字列へ
SQL Server の 2016 以降では、組み込みの JSON ファンクションが提供されて、簡単に T-SQL でクエリの結果を JSON 文字列に変換したり、JSON 文字列を解析して、値を取得したりクエリーを書いたりできるようになりました。
今回は、T-SQL でクエリの結果を JSON 文字列に変換してみたいと思います。
クエリーの結果を FOR JSON で JSON 形式の文字列へ
クエリーの結果を JSON 形式の文字列にフォーマットするには、FOR JSON 句を SELECT 文に追加します。
FOR JSON には、FOR JSON AUTO と FOR JSON PATH があります。
FOR JSON AUTO は、SELECT 文の構造に応じて、クエリーの結果を自動的に JSON 形式に変換します。
SELECT 句で何も指定しなくてもいいので、てっとり早いですが、プロパティーのキー名や階層など、細かい指定はできません。
FOR JSON PATH のほうは、オブジェクトをネストさせたり、いろいろと自由に制御することができます。
JSON を扱いたいような状況では、FOR JSON PATH を使っていろいろと制御して、JSON 文字列を生成しなければならないケースのほうが多いのではないかな？と思います。
FOR JSON AUTO
それでは、まず FOR JSON AUTO の出力例を見てみましょう。
次のような Student テーブルと TestResult テーブルがあります。
このようなクエリーの結果を、JSON 文字列に変換してみます。
※ サンプルデータを生成するスクリプトは こちら にありますが、同じ名前のテーブルが既に存在している場合、削除されてしまうのでご注意ください。
FOR JSON AUTO をつかって、自動的に JSON 形式に変換された文字列を取得するクエリーは以下の通りです。
SELECT TOP 2 S.StudentID, S.FirstName, S.LastName, TR.TestResultCount, TR.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 AUTO;
[ 実行結果 ]
結果はテキストに出力しています。
ちなみに結果をグリッドに出力すると、次のように JSON 文字列がリンク付きで表示され、クリックすると別画面が開いて文字列が表示されます。
得られた JSON の文字列を読みやすいように改行すると次のようになります。
[
{
"StudentID":3,
"FirstName":"Yuko",
"LastName":"Suzuki",
"TR":[
{
"TestResultCount":1,
"ScoreAverage":92.000000
}
]
},
{
"StudentID":4,
"FirstName":"Takao",
"LastName":"Sato",
"TR":[
{
"TestResultCount":2,
"ScoreAverage":89.500000
}
]
}
]
試しにもうひとつ、次のようなクエリーを変換してみます。
SELECT TOP 2 S.StudentID, S.FirstName, S.LastName, T.TestNameEn, TR.Score FROM Student AS S INNER JOIN TestResult AS TR ON S.StudentID = TR.StudentID INNER JOIN Test AS T ON TR.TestID = T.TestID WHERE T.TestID = 1 ORDER BY TR.Score DESC FOR JSON AUTO;
[ 実行結果 ]
[
{
"StudentID":4,
"FirstName":"Takao",
"LastName":"Sato",
"T":[
{
"TestNameEn":"Math 1",
"TR":[
{
"Score":98
}
]
}
]
},
{
"StudentID":1,
"FirstName":"Taro",
"LastName":"Yamada",
"T":[
{
"TestNameEn":"Math 1",
"TR":[
{
"Score":85
}
]
}
]
}
]
要素数は行数と同じで、カラム名がプロパティーのキー名になります。
JOIN した場合はテーブル名やテーブルのエイリアスがプロパティーのキーになり、オブジェクトがネストされます。
すごくシンプルな SELECT 文に使う時はよさそうですが、複雑なクエリー結果に使う時は、期待通りにフォーマットされた JSON 文字列 が返ってくるか少しわかりにくいので、FOR JSON PATH で自分で制御したほうが良いかもしれません。