Overview

• Convert JSON collections to a rowset:

DECLARE @json NVARCHAR(4000) =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  
   
SELECT * 
FROM OPENJSON(@json)
WITH 
(
	Id int 'strict $.id', 
	FirstName nvarchar(50) '$.info.name', 
	LastName nvarchar(50) '$.info.surname',  
	Age int, 
	DateOfBirth datetime2 '$.dob'
)

• Convert SQL Server data to JSON or export JSON:

SELECT TOP 10 ProductID, [Name]
FROM Production.Product
FOR JSON PATH