サーチ…


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

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

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);
キータイプ
ジョー 1
年齢 27 2
スキル ["C#"、 "SQL"] 4

列の型は、値の型、つまり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など)、無視されます。値は自動的に指定された型に変換されます。

日付顧客
SO43659 2011-05-31T00:00:00 MSFT 1
SO43661 2011-06-01T00:00:00 ノキア 3

ネストされた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オブジェクト内のキーは、これらのパスによってフェッチされます。値は自動的に指定された型に変換されます。

日付顧客
SO43659 2011-05-31T00:00:00 MSFT 1
SO43661 2011-06-01T00:00:00 ノキア 3

内部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"オブジェクトにマップされます。結果は次のようになります。

日付情報
SO43659 2011-05-31T00:00:00 {"customer": "MSFT"、 "Price":59.99、 "qty":1}
SO43661 2011-06-01T00:00:00 {"customer": "Nokia"、 "Price":24.99、 "qty":3}

ネストされた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)

結果:

日付アイテム価格
SO43659 2011-05-31 00:00:00.000 [{"Price":11.99、 "Quantity":1}、{"Price":12.99、 "Quantity":5}] 11.99 1
SO43659 2011-05-31 00:00:00.000 [{"Price":11.99、 "Quantity":1}、{"Price":12.99、 "Quantity":5}] 12.99 5
SO43661 2011-06-01 00:00:00.000 [{"価格":21.99、 "数量":3}、{"価格":22.99、 "数量":2}、{"価格":23.99、 "数量":2}] 21.99 3
SO43661 2011-06-01 00:00:00.000 [{"価格":21.99、 "数量":3}、{"価格":22.99、 "数量":2}、{"価格":23.99、 "数量":2}] 22.99 2
SO43661 2011-06-01 00:00:00.000 [{"価格":21.99、 "数量":3}、{"価格":22.99、 "数量":2}、{"価格":23.99、 "数量":2}] 23.99 2


Modified text is an extract of the original Stack Overflow Documentation
ライセンスを受けた CC BY-SA 3.0
所属していない Stack Overflow