Microsoft SQL Server OPENJSON Extracting inner JSON sub-objects

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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:

NumberDateInfo
SO436592011-05-31T00:00:00{"customer":"MSFT","Price":59.99,"qty":1}
SO436612011-06-01T00:00:00{"customer":"Nokia","Price":24.99,"qty":3}


Got any Microsoft SQL Server Question?