Microsoft SQL Server
OPENJSON

Getキー：JSONテキストからの値のペア

OPENJSON関数はJSONテキストを解析し、JSONの最初のレベルですべてのkey：valueのペアを返します：

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);

列の型は、値の型、つまりnull（0）、文字列（1）、数値（2）、ブール値（3）、配列（4）、およびオブジェクト（5）を表します。

JSON配列を行セットに変換する

OPENJSON関数はJSONオブジェクトのコレクションを解析し、JSONテキストの値を行のセットとして返します。

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

WITH句にOPENJSON関数の戻りスキーマが指定されています。 JSONオブジェクトのキーは列名で取得されます。 JSONの一部のキーがWITH句で指定されていない場合（この例のPriceなど）、無視されます。値は自動的に指定された型に変換されます。

ネストされたJSONフィールドを行セットに変換する

OPENJSON関数はJSONオブジェクトのコレクションを解析し、JSONテキストの値を行のセットとして返します。入力オブジェクトの値がネストされている場合、WITH句の各列に追加のマッピングパラメータを指定できます。

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

WITH句にOPENJSON関数の戻りスキーマが指定されています。 typeが指定された後、戻り値が見つかるはずのJSONノードへのパス。 JSONオブジェクト内のキーは、これらのパスによってフェッチされます。値は自動的に指定された型に変換されます。

内部JSONサブオブジェクトの抽出

OPENJSONはJSONテキスト内のJSONオブジェクトの断片を抽出できます。 JSONサブオブジェクトを参照する列定義で、nvarchar（max）型とAS JSONオプションを設定します。

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

Info列は "Info"オブジェクトにマップされます。結果は次のようになります。

ネストされたJSONサブ配列の操作

JSONは内部配列と複雑な構造を持つことがあります。この例では、OrderItemsのネストされたサブ配列を持つ配列の配列があります。

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

Items array AS JSONフラグメントを返すOPENJSONを使用して、ルートレベルのプロパティを解析できます。次に、Items配列に対してOPENJSONを再度適用し、内部のJSONテーブルを開きます。最初のレベルのテーブルと内部テーブルは、標準テーブル間のJOINのように「結合」されます。

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

結果：