T-SQL クエリーで JSON 文字列から値を取得する

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

今回は、T-SQL クエリーで JSON_VALUE や JSON_QUERY を用いて、JSON 文字列から値を取得してみます。

JSON_VALUE

JSON 文字列からスカラー値（ひとつの値）を取り出したい時は組み込み関数の JSON_VALUE　を使って取得することができます。構文は次の通りです。

JSON_VALUE ( [JSON 文字列] , [取得したいプロパティのパス] )

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

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

例えば、前回「 FOR JSON PATH 」で生成した JSON 文字列から JSON_VALUE を使って値を取得するクエリーは次の通りです。

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  JSON_VALUE(@JSONString,'$[0].StudentID') AS StudentID_0,
            JSON_VALUE(@JSONString,'$[0].StudentInfo.LastName') AS LastName_0,
            JSON_VALUE(@JSONString,'$[0].ScoreAverage') AS ScoreAverage_0,
            JSON_VALUE(@JSONString,'$[1].StudentID') AS StudentID_1,
            JSON_VALUE(@JSONString,'$[1].StudentInfo.LastName') AS LastName_1,
            JSON_VALUE(@JSONString,'$[1].ScoreAverage') AS ScoreAverage_1,
            JSON_VALUE(@JSONString,'$[2].ScoreAverage2') AS ScoreAverage_2;

END
ELSE
BEGIN

    SELECT '@JSONString is invalid.';

END

[ 実行結果 ]

「取得したいプロパティのパス」は、フォーマットが間違えている場合にはエラーになりますが、一番下の ScoreAverage_2 のように、存在しないプロパティーを指定してもエラーにはならず NULL が返ってくるだけです。

JSON_QUERY

JSON 文字列からオブジェクトや配列を取り出したい時は組み込み関数の JSON_QUERY　を使って取得することができます。構文は次の通りです。

JSON_QUERY ( [JSON 文字列] [, [取得したいプロパティのパス（オプショナル）] )

例えば、先ほどと同様の JSON 文字列から、ひとつめのオブジェクトの StudentInfo の部分と、ふたつめのオブジェクトのみを取り出したいような時は次のように取得することができます。

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  JSON_QUERY(@JSONString,'$[0].StudentInfo') AS StudentInfo_0,
            JSON_QUERY(@JSONString,'$[1]') AS Student_1;

END
ELSE
BEGIN

    SELECT '@JSONString is invalid.';

END

[ 実行結果 ]

StudentInfo_0
{ "FirstName":"Yuko", "LastName":"Suzuki" }

Student_1
{ "StudentID":4, "StudentInfo":{ "FirstName":"Takao", "LastName":"Sato" }, "TestResultCount":2, "ScoreAverage":89.500000 }

それぞれ、期待していたオブジェクトの JSON 文字列が取得できました。