T-SQL クエリーで JSON 文字列をテーブル形式に変換する

T-SQL クエリーで JSON 文字列をテーブル形式に変換する

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

今回は、テーブル値関数の OPENJSON を使って、T-SQL クエリーで JSON 文字列をテーブル形式に変換してみましょう。

OPENJSON

JSON 文字列をテーブル形式の値に変換したい時は OPENJSON　を使って行うことができます。 構文は次の通りです。

OPENJSON( JSON文字列 [ , パス ] )  [  ]

 ::= WITH ( { カラム名 データ型 [ カラムパス ] [ AS JSON ] } [ ,...n ] )

インプットの JSON 文字列のフォーマットが正しくない (invalid な) 場合は OPENJSON がエラーを返します。

OPENJSON を使う前に、ISJSON ([JSON 文字列]) を使って、インプットの JSON 文字列が有効なものかチェックしたほうが良いと思います。

例えば、前回 「 FOR JSON PATH 」 で生成した JSON 文字列をテーブル形式するクエリーは次の通りです。

DECLARE @JSONString NVARCHAR(MAX) =
'[
   {
      "StudentID":3,
      "StudentInfo":{
         "FirstName":"Yuko",
         "LastName":"Suzuki"
      },
      "TestResultCount":1,
      "ScoreAverage":92.000000
   },
   {
      "StudentID":4,
      "StudentInfo":{
         "FirstName":"Takao",
         "LastName":"Sato"
      },
      "TestResultCount":2,
      "ScoreAverage":89.500000
   }
]';

IF ISJSON(@JSONString) = 1
BEGIN

    SELECT *
    FROM OPENJSON(@JSONString)
    WITH (      
        StudentID           INT '$.StudentID',  
        FirstName           NVARCHAR(100) '$.StudentInfo.FirstName',
        LastName            NVARCHAR(100) '$.StudentInfo.LastName',
        TestResultCount     INT '$.TestResultCount',
        ScoreAverage        DECIMAL(5,2) '$.ScoreAverage'
    );

END
ELSE
BEGIN

    SELECT '@JSONString is invalid.';

END

[ 実行結果 ]

オブジェクトがネストされていても、'$.StudentInfo.FirstName' のようにドットで指定すれば同じ階層のカラムとして値を取得できます。

OPENJSON はデフォルトで、WITH 句で指定したカラム名とマッチするキー名の値を取得するので、名前が同じ場合はカラムパスは指定しなくても大丈夫です。

WITH 句で指定したカラム名のキー名が存在せず、カラムパスも指定されていない場合はエラーにはならず、値が NULL になります。