Skip to content

Instantly share code, notes, and snippets.

@ncalm

ncalm/Part 1 of Working with JSON in SQL Server.sql

Last active November 8, 2022 15:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ncalm/520b5048a01b99d272406b9dcd07b59a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ncalm/520b5048a01b99d272406b9dcd07b59a to your computer and use it in GitHub Desktop.
Download ZIP
Accompanying scripts for "SQL Server - Working with JSON" video series
Raw
Part 1 of Working with JSON in SQL Server.sql
/*
Simple example of FOR JSON PATH
With FOR JSON PATH, we can convert table data to JSON structured text.
By using the . notation, we can create hierarchies of related attributes.
*/
SELECT TOP 5 *
FROM DimCustomer;
SELECT TOP 1
CustomerKey,
FirstName AS "name.first",
MiddleName AS "name.middle",
LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french"
FROM DimCustomer AS c
FOR JSON PATH;
/*
The . notation can also use square brackets instead of double quotes.
*/
SELECT TOP 5
CustomerKey,
FirstName AS [name.first],
MiddleName AS [name.middle],
LastName AS [name.last],
BirthDate,
EnglishEducation AS [education.english],
SpanishEducation AS [education.spanish],
FrenchEducation AS [education.french],
EnglishOccupation AS [occupation.english],
SpanishOccupation AS [occupation.spanish],
FrenchOccupation AS [occupation.french]
FROM DimCustomer AS c
FOR JSON PATH;
/*
We can't use SELECT .. INTO with FOR JSON PATH
"The FOR JSON clause is not allowed in a SELECT INTO statement."
*/
SELECT TOP 5
CustomerKey,
FirstName AS "name.first",
MiddleName AS "name.middle",
LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french"
INTO #customers_json
FROM DimCustomer AS c
FOR JSON PATH;
/*
We can't use FOR JSON PATH in an INSERT statement either :(
"The FOR JSON clause is not allowed in a INSERT statement."
*/
DROP TABLE IF EXISTS #customers_json;
CREATE TABLE #customers_json (customers nvarchar(max));
INSERT INTO #customers_json (customers)
SELECT CustomerKey,
FirstName AS "name.first",
MiddleName AS "name.middle",
LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french"
FROM DimCustomer AS c
FOR JSON PATH;
SELECT customers
FROM #customer_json;
/*
But we can use a CTE and insert the results of a query on the CTE.
We need to provide an alias for the single column that is created.
We do this in the 'WITH' clause.
By adding the ROOT('') option, we can give the json object
a root node.
*/
DROP TABLE IF EXISTS #customers_json;
CREATE TABLE #customers_json (customers nvarchar(max));
WITH customer_json (customers)
AS
(
SELECT CustomerKey,
FirstName AS "name.first",
MiddleName AS "name.middle",
LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french"
FROM DimCustomer AS c
FOR JSON PATH
)
INSERT INTO #customers_json (customers)
SELECT customers
FROM customer_json;
SELECT customers
FROM #customers_json;
/*
By adding the ROOT('') option, we can give the json object
a root node.
*/
SELECT CustomerKey,
FirstName AS "name.first",
MiddleName AS "name.middle",
LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french"
FROM DimCustomer AS c
FOR JSON PATH, ROOT('Customers');
/*
We can also create more complex JSON objects from joined tables
First let's review the available columns in the Geography dimension;
*/
SELECT TOP 10 * from DimGeography;
/*
Now let's add a "location" node to our customers JSON:
*/
SELECT TOP 5
c.CustomerKey,
c.FirstName AS "name.first",
c.MiddleName AS "name.middle",
c.LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french",
g.City AS "location.city",
g.EnglishCountryRegionName AS "location.country_region.english",
g.SpanishCountryRegionName AS "location.country_region.spanish",
g.FrenchCountryRegionName AS "location.country_region.french"
FROM DimCustomer AS c
INNER JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
FOR JSON PATH, ROOT('Customers');
/*
The data in the sales territory dimension is quite simple:
*/
SELECT * FROM DimSalesTerritory;
/*
We can nest the sales territory information beneath the location:
*/
SELECT TOP 5
c.CustomerKey,
c.FirstName AS "name.first",
c.MiddleName AS "name.middle",
c.LastName AS "name.last",
BirthDate,
EnglishEducation AS "education.english",
SpanishEducation AS "education.spanish",
FrenchEducation AS "education.french",
EnglishOccupation AS "occupation.english",
SpanishOccupation AS "occupation.spanish",
FrenchOccupation AS "occupation.french",
g.City AS "location.city",
g.EnglishCountryRegionName AS "location.country_region.english",
g.SpanishCountryRegionName AS "location.country_region.spanish",
g.FrenchCountryRegionName AS "location.country_region.french",
s.SalesTerritoryRegion AS "location.sales_territory.region",
s.SalesTerritoryGroup AS "location.sales_territory.group",
s.SalesTerritoryImage AS "location.sales_territory.image"
FROM DimCustomer AS c
INNER JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
INNER JOIN DimSalesTerritory AS s ON g.SalesTerritoryKey = s.SalesTerritoryKey
FOR JSON PATH, ROOT('Customers');
Raw
Part 2 of Working with JSON in SQL Server.sql
/*
Convert some table data to JSON and put it in a temp table
*/
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (customers nvarchar(max));
WITH customer_json (customers)
AS
(
SELECT TOP 5
c.CustomerKey,
c.FirstName AS "name.first",
c.MiddleName AS "name.middle",
c.LastName AS "name.last",
c.BirthDate,
c.EnglishEducation AS "education.english",
c.SpanishEducation AS "education.spanish",
c.FrenchEducation AS "education.french",
c.EnglishOccupation AS "occupation.english",
c.SpanishOccupation AS "occupation.spanish",
c.FrenchOccupation AS "occupation.french",
g.City AS "location.city",
g.EnglishCountryRegionName AS "location.country_region.english",
g.SpanishCountryRegionName AS "location.country_region.spanish",
g.FrenchCountryRegionName AS "location.country_region.french",
s.SalesTerritoryRegion AS "location.sales_territory.region",
s.SalesTerritoryGroup AS "location.sales_territory.group"
FROM DimCustomer AS c
INNER JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
INNER JOIN DimSalesTerritory AS s ON g.SalesTerritoryKey = s.SalesTerritoryKey
FOR JSON PATH--, ROOT('Customers')
)
INSERT INTO #t (customers)
SELECT customers
FROM customer_json;
GO
/*
We use OPENJSON to extract data from a json object in a variable
If we don't specify a schema for the object, we get three columns:
Key
Value
Type
*/
DECLARE @json AS nvarchar(4000) = (SELECT customers FROM #t);
SELECT *
FROM OPENJSON(@json);
SELECT *
FROM OPENJSON((SELECT customers FROM #t));
--2
/*
We use OPENJSON to extract data from a json object in a variable
If we remove the ROOT, we get something slightly more useful with one row per customer
*/
SELECT *
FROM OPENJSON((SELECT customers FROM #t));
--3
/*
If we use a schema, we start to get more sensible results
We do this by using the WITH clause of OPENJSON
We specify each column we want to extract by providing a column name, a data type
and the path to the json element that contains the data we want to put in that column
*/
SELECT *
FROM OPENJSON((SELECT customers FROM #t))
WITH (
CustomerKey INT '$.CustomerKey',
FirstName nvarchar(100) '$.name.first',
MiddleName nvarchar(100) '$.name.middle',
LastName nvarchar(100) '$.name.last'
)
;
--4
/*
We can also choose to return portions of the object
as an nvarchar(max) column of json objects
For example, let's put the sales territory in a column
*/
SELECT *
FROM OPENJSON((SELECT customers FROM #t))
WITH (
CustomerKey INT '$.CustomerKey',
FirstName nvarchar(100) '$.name.first',
MiddleName nvarchar(100) '$.name.middle',
LastName nvarchar(100) '$.name.last',
SalesTerritory nvarchar(max) '$.location.sales_territory' AS JSON
)
;
--5
/*
Further to this, we can then selectively extract specific elements from the
JSON object in the column.
We do this using the JSON_VALUE function:
We pass the column name containing the JSON as the first parameter
and the path within that object to the element we want to return
as the second parameter
*/
SELECT CustomerKey, FirstName, MiddleName, LastName,
JSON_VALUE(SalesTerritory, '$.group') AS SalesTerritoryGroup,
SalesTerritory
FROM OPENJSON((SELECT customers FROM #t))
WITH (
CustomerKey INT '$.CustomerKey',
FirstName nvarchar(100) '$.name.first',
MiddleName nvarchar(100) '$.name.middle',
LastName nvarchar(100) '$.name.last',
SalesTerritory nvarchar(max) '$.location.sales_territory' AS JSON
)
;
--6
/*
We can skip the WITH clause altogether and just use JSON_VALUE
The problem here is that we can't return nested json objets into columns
*/
SELECT JSON_VALUE([value], '$.name.first') AS FirstName,
JSON_VALUE([value], '$.name.middle') AS MiddleName,
JSON_VALUE([value], '$.name.last') AS LastName,
JSON_VALUE([value], '$.location.sales_territory.group') AS SalesTerritoryGroup
FROM OPENJSON((SELECT customers FROM #t));
Raw
Part 3 json data - customers.json
[
{
"CustomerKey": 11000,
"name": {
"first": "Jon",
"middle": "V",
"last": "Yang"
},
"BirthDate": "1971-10-06",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Rockhampton",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11001,
"name": {
"first": "Eugene",
"middle": "L",
"last": "Huang"
},
"BirthDate": "1976-05-10",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Seaford",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11002,
"name": {
"first": "Ruben",
"last": "Torres"
},
"BirthDate": "1971-02-09",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Hobart",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11003,
"name": {
"first": "Christy",
"last": "Zhu"
},
"BirthDate": "1973-08-14",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "North Ryde",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11004,
"name": {
"first": "Elizabeth",
"last": "Johnson"
},
"BirthDate": "1979-08-05",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Wollongong",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
}
]
Raw
Part 3 json data - customers_with_root.json
{
"Customers": [
{
"CustomerKey": 11000,
"name": {
"first": "Jon",
"middle": "V",
"last": "Yang"
},
"BirthDate": "1971-10-06",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Rockhampton",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11001,
"name": {
"first": "Eugene",
"middle": "L",
"last": "Huang"
},
"BirthDate": "1976-05-10",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Seaford",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11002,
"name": {
"first": "Ruben",
"last": "Torres"
},
"BirthDate": "1971-02-09",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Hobart",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11003,
"name": {
"first": "Christy",
"last": "Zhu"
},
"BirthDate": "1973-08-14",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "North Ryde",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
},
{
"CustomerKey": 11004,
"name": {
"first": "Elizabeth",
"last": "Johnson"
},
"BirthDate": "1979-08-05",
"education": {
"english": "Bachelors",
"spanish": "Licenciatura",
"french": "Bac + 4"
},
"occupation": {
"english": "Professional",
"spanish": "Profesional",
"french": "Cadre"
},
"location": {
"city": "Wollongong",
"country_region": {
"english": "Australia",
"spanish": "Australia",
"french": "Australie"
},
"sales_territory": {
"region": "Australia",
"group": "Pacific"
}
}
}
]
}
Raw
Part 3 of Working with JSON in SQL Server.sql
/*
We can read a JSON file using OPENROWSET with the BULK option
...FROM OPENROWSET(BULK file_path, SINGLE_CLOB) AS alias
SINGLE_CLOB = single character large object
This table returns has a single column called BulkColumn
This example uses the customers.json file which contains
a JSON array (i.e. enclosed in square brackets) of 5
customers from the AdventureWorksDW2019 DimCustomer table
*/
SELECT *
FROM OPENROWSET(BULK 'C:\tmp\temp data for SQL\customers.json', SINGLE_CLOB) AS j;
/*
We can easily assign the object in the BulkColumn column to a variable
then use OPENJSON to read the json in the variable
*/
DECLARE @json nvarchar(max);
SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'C:\tmp\temp data for SQL\customers.json', SINGLE_CLOB) AS j;
SELECT *
FROM OPENJSON(@json);
/*
Alternatively, we can apply the OPENJSON function to the JSON in BulkColumn
and begin extracting useful information from the file immediately
*/
SELECT CustomerKey, FirstName, LastName, SalesTerritoryGroup, Customer
FROM OPENROWSET(BULK 'C:\tmp\temp data for SQL\customers.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
CustomerKey int '$.CustomerKey',
FirstName varchar(50) '$.name.first',
MiddleName varchar(50) '$.name.middle',
LastName varchar(50) '$.name.last',
SalesTerritoryGroup varchar(50) '$.location.sales_territory.group',
Customer nvarchar(max) '$' AS JSON
);
/*
If the file is a JSON object and not a JSON array, then
we only get one row (the Customers JSON array)
*/
DECLARE @json nvarchar(max);
SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'C:\tmp\temp data for SQL\customers_with_root.json', SINGLE_CLOB) AS j;
SELECT *
FROM OPENJSON(@json);
/*
Since there is no CustomerKey in the root object (it is in the nested Customers object)
we might think we can extract it like this
*/
SELECT CustomerKey
FROM OPENROWSET(BULK 'C:\tmp\temp data for SQL\customers_with_root.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn)
WITH (CustomerKey int '$.Customers.CustomerKey') c;
/*
The Customers object is a JSON array of
individual customers. It is not an object with nodes of its own.
So, we must first open the root node, and then open the json array.
Then we must use the WITH clause on the json array to extract
the keys of each customer.
*/
SELECT
c.CustomerKey,
c.FirstName,
c.LastName,
c.SalesTerritoryGroup
FROM OPENROWSET(
BULK 'C:\tmp\temp data for SQL\customers_with_root.json',
SINGLE_CLOB
) AS j
CROSS APPLY OPENJSON(BulkColumn) --open the customers root node
WITH (Customers nvarchar(max) '$.Customers' AS JSON) AS r
CROSS APPLY OPENJSON(r.Customers) --open the JSON array
WITH (
CustomerKey int '$.CustomerKey',
FirstName varchar(50) '$.name.first',
MiddleName varchar(50) '$.name.middle',
LastName varchar(50) '$.name.last',
SalesTerritoryGroup varchar(50) '$.location.sales_territory.group'
) AS c; --extract data from the individual customers
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment