提取內部 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
  )

資訊列將對映到資訊物件。結果將是:

日期 資訊
SO43659 2011-05-31T00:00:00 { 客戶MSFT價格:59.99, 數量:1}
SO43661 2011-06-01T00:00:00 { 客戶諾基亞價格:24.99, 數量:3}