Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@samirbehara-zz
Created April 30, 2016 21:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save samirbehara-zz/f38aa182af60a63e5069be3f67b1f668 to your computer and use it in GitHub Desktop.
Save samirbehara-zz/f38aa182af60a63e5069be3f67b1f668 to your computer and use it in GitHub Desktop.
This script contains the examples of JSON usage in SQL Server 2016
-- 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
);
----------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment