This script contains the examples of JSON usage in SQL Server 2016

JSON Scripts - SQL Server 2016.sql

-- Return results as a JSON text by using AUTO mode
SELECT TOP 2 [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]     
  FROM [AdventureWorks2016CTP3].[Person].[Address]
  FOR JSON AUTO
  --------------------------------------------------------------------------
  -- Convert JSON text into table rows using OPENJSON function
  DECLARE @json nvarchar(max) 
  SET @json = N'[
  {
    "AddressID": 532,
    "AddressLine1": "#500-75 Connor Street",
    "City": "Ottawa",
    "StateProvinceID": 57,
    "PostalCode": "K4B 1S2"
  },
  {
    "AddressID": 497,
    "AddressLine1": "#9900 2700 Production Way",
    "City": "Burnaby",
    "StateProvinceID": 7,
    "PostalCode": "V5A 4X1"
  }
]'
SELECT * FROM OPENJSON(@json)
----------------------------------------------------------------------------
-- By using PATH mode, we have more control over the JSON text
SELECT Per.FirstName, Per.LastName, 
Cust.AccountNumber, Cust.ModifiedDate
FROM Person.Person Per
INNER JOIN Sales.Customer Cust
ON Cust.PersonID = Per.BusinessEntityID
WHERE Per.BusinessEntityID < 295
FOR JSON PATH, ROOT('Person') -- Use the ROOT option to specify the root.

 DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "FirstName": "Gustavo",
      "LastName": "Achong",
      "AccountNumber": "AW00029484",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    },
    {
      "FirstName": "Catherine",
      "LastName": "Abel",
      "AccountNumber": "AW00029485",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    }
  ]
}'

SELECT [key], value 
FROM OPENJSON(@json, '$.Person[0]')
-------------------------------------------------------------------------------
-- Flaten the result set into a single row using WITH clause
SELECT Per.FirstName, Per.LastName, 
Cust.AccountNumber, Cust.ModifiedDate
FROM Person.Person Per
INNER JOIN Sales.Customer Cust
ON Cust.PersonID = Per.BusinessEntityID
WHERE Per.BusinessEntityID < 295
FOR JSON PATH, ROOT('Person')

 DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "FirstName": "Gustavo",
      "LastName": "Achong",
      "AccountNumber": "AW00029484",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    },
    {
      "FirstName": "Catherine",
      "LastName": "Abel",
      "AccountNumber": "AW00029485",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    }
  ]
}'

--Use the WITH clause with the OPENJSON function to define a schema.
SELECT *
FROM OPENJSON(@json, '$.Person[0]')
WITH 
([FirstName] nvarchar(50),
 [LastName] nvarchar(50) ,
 [AccountNumber] varchar(10),
 [ModifiedDate] datetime
);

----------------------------------------------------------------------------------------------