SQL Server 入門 SQL Server 入門

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

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 AUTOFOR JSON PATH があります。


FOR JSON AUTO は、SELECT 文の構造に応じて、クエリーの結果を自動的に JSON 形式に変換します。

SELECT 句で何も指定しなくてもいいので、てっとり早いですが、プロパティーのキー名や階層など、細かい指定はできません。


FOR JSON PATH のほうは、オブジェクトをネストさせたり、いろいろと自由に制御することができます。

JSON を扱いたいような状況では、FOR JSON PATH を使っていろいろと制御して、JSON 文字列を生成しなければならないケースのほうが多いのではないかな?と思います。


FOR JSON AUTO

それでは、まず FOR JSON AUTO の出力例を見てみましょう。

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

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

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

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

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


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;

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

結果はテキストに出力しています。

ちなみに結果をグリッドに出力すると、次のように JSON 文字列がリンク付きで表示され、クリックすると別画面が開いて文字列が表示されます。

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


得られた 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;

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

[
   {
      "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 で自分で制御したほうが良いかもしれません。


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

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