Tutorial by Examples

OPENJSON function parse JSON text and returns all key:value pairs at the first level of JSON: declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}'; SELECT * FROM OPENJSON(@json); keyvaluetypeNameJoe1age272ski...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Q...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. If the values in input object are nested, additional mapping parameter can be specified in each column in WITH clause: declare @json nvarchar(4000) = N'[ {"data":{"num":&quot...

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"...

JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00", "Items":[{"Price"...