Microsoft SQL Server OPENJSON Extracting inner JSON sub-objects
Example
OPENJSON can extract fragments of JSON objects inside the JSON text. In the column definition that references JSON sub-object set the type nvarchar(max) and AS JSON option:
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 column will be mapped to "Info" object. Results will be:
| Number | Date | 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} |
