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