John McCormack DBA

SQL Server Databases and Cloud

JSON support for SQL Server 2016

By Leave a Comment

JSON and SQL Server 2016

This post is an introduction to the new JSON support for SQL Server 2016. The post is an entry level post, suitable for someone who is using JSON methods for the first time but who does have some knowledge of T-SQL and a basic understanding of what JSON is.

Background:

JSON support has been introduced into SQL Server for the first time in SQL Server 2016. It was a highly requested feature on Microsoft Connect. Since there are new methods added to SQL Server, I wanted to test them out and get to understand them.

The code:

This code will only work in SQL Server 2016 so you need to have an instance of 2016 available to use for the demo. You can also use Azure SQL Database. See announcement.

Database compatibility level 130 is required for OPENJSON function. Other functions are available in all levels. Note that you need to have new V12 server.

—

Step 1

Use an existing database or create a new one. I created one called Learning. Then create a table to use for the demo called Weather.

[sql]

USE Learning;

DROP TABLE IF EXISTS Weather; — This syntax is new too.
CREATE TABLE Weather
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
DateInserted datetime DEFAULT GETDATE(),
WeatherDetail NVARCHAR(max) — No JSON data type, we just use NVARCHAR(max)
)

[/sql]

Step 2

Get a JSON source that you want to use. There are a number of free data sources available online. For the demo, I have used the local weather forecast in JSON format from Yahoo weather. https://developer.yahoo.com/weather/

Now that we have a table and some valid JSON, we want to insert the JSON text into the table we just created. Replace the values for Location and WeatherDetail in the code below with your own values with your own or simply just run the code I have provided below.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES
(‘Paisley’,
‘{
“query”: {
“count”: 1,
“created”: “2016-04-05T17:39:05Z”,
“lang”: “en-GB”,
“results”: {
“channel”: {
“units”: {
“distance”: “mi”,
“pressure”: “in”,
“speed”: “mph”,
“temperature”: “F”
},
“title”: “Yahoo! Weather – Paisley, Scotland, GB”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“description”: “Yahoo! Weather for Paisley, Scotland, GB”,
“language”: “en-us”,
“lastBuildDate”: “Tue, 05 Apr 2016 06:39 PM BST”,
“ttl”: “60”,
“location”: {
“city”: “Paisley”,
“country”: “United Kingdom”,
“region”: ” Scotland”
},
“wind”: {
“chill”: “48”,
“direction”: “270”,
“speed”: “18”
},
“atmosphere”: {
“humidity”: “63”,
“pressure”: “991.0”,
“rising”: “0”,
“visibility”: “16.1”
},
“astronomy”: {
“sunrise”: “6:34 am”,
“sunset”: “8:7 pm”
},
“image”: {
“title”: “Yahoo! Weather”,
“width”: “142”,
“height”: “18”,
“link”: “http://weather.yahoo.com”,
“url”: “http://l.yimg.com/a/i/brand/purplelogo//uh/us/news-wea.gif”
},
“item”: {
“title”: “Conditions for Paisley, Scotland, GB at 05:00 PM BST”,
“lat”: “55.842018”,
“long”: “-4.42209”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“pubDate”: “Tue, 05 Apr 2016 05:00 PM BST”,
“condition”: {
“code”: “30”,
“date”: “Tue, 05 Apr 2016 05:00 PM BST”,
“temp”: “52”,
“text”: “Partly Cloudy”
},
“forecast”: [
{
“code”: “12”,
“date”: “05 Apr 2016”,
“day”: “Tue”,
“high”: “53”,
“low”: “44”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “06 Apr 2016”,
“day”: “Wed”,
“high”: “47”,
“low”: “41”,
“text”: “Showers”
},
{
“code”: “30”,
“date”: “07 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Partly Cloudy”
},
{
“code”: “39”,
“date”: “08 Apr 2016”,
“day”: “Fri”,
“high”: “49”,
“low”: “39”,
“text”: “Scattered Showers”
},
{
“code”: “12”,
“date”: “09 Apr 2016”,
“day”: “Sat”,
“high”: “49”,
“low”: “41”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “10 Apr 2016”,
“day”: “Sun”,
“high”: “51”,
“low”: “40”,
“text”: “Showers”
},
{
“code”: “28”,
“date”: “11 Apr 2016”,
“day”: “Mon”,
“high”: “53”,
“low”: “41”,
“text”: “Mostly Cloudy”
},
{
“code”: “12”,
“date”: “12 Apr 2016”,
“day”: “Tue”,
“high”: “52”,
“low”: “42”,
“text”: “Rain”
},
{
“code”: “39”,
“date”: “13 Apr 2016”,
“day”: “Wed”,
“high”: “53”,
“low”: “41”,
“text”: “Scattered Showers”
},
{
“code”: “5”,
“date”: “14 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Rain And Snow”
}
],
“description”: “<![CDATA[<img src=\”http://l.yimg.com/a/i/us/we/52/30.gif\”/>\n<BR />\n<b>Current Conditions:</b>\n<BR />Partly Cloudy\n<BR />\n<BR />\n<b>Forecast:</b>\n<BR /> Tue – Rain. High: 53Low: 44\n<BR /> Wed – Showers. High: 47Low: 41\n<BR /> Thu – Partly Cloudy. High: 53Low: 40\n<BR /> Fri – Scattered Showers. High: 49Low: 39\n<BR /> Sat – Rain. High: 49Low: 41\n<BR />\n<BR />\n<a href=\”http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/\”>Full Forecast at Yahoo! Weather</a>\n<BR />\n<BR />\n(provided by <a href=\”http://www.weather.com\” >The Weather Channel</a>)\n<BR />\n]]>”,
“guid”: {
“isPermaLink”: “false”
}
}
}
}
}
}’
)

[/sql]

Step 3

Check your table contents by runnning [sql]SELECT * FROM [dbo].[Weather];[/sql]

Step 4 – ISJSON

The ISJSON built-in function is used to verify that your JSON text is properly formatted. The queries below select the values for Location and WeatherDetail from dbo.Weather and also the return value of the ISJSON function against these values. We can see that the value of Location is Paisley and that this is not a valid JSON value. We also see the value of WeatherDetail and that this IS a valid JSON value.

[sql]

SELECT [Location] FROM [dbo].[Weather];
SELECT ISJSON([Location]) as [ISJSON] FROM [dbo].[Weather];
SELECT WeatherDetail FROM [dbo].[Weather];
SELECT ISJSON(WeatherDetail) as [ISJSON] FROM [dbo].[Weather];

[/sql]

Step 5

Let’s insert another JSON value with fewer details into dbo.Weather. This will help us when we try to query it later.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES (‘Caleta de Fuste’,'{
“Forecasts”:[
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00AM”,
“WeatherDetails”:”Cloud”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 9:00AM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00PM”,
“WeatherDetails”:”Windy”
}
}
]
}’
)

[/sql]

Let’s just check what we have in the table (and it’s validity) before we move on.

[sql]

SELECT *, ISJSON(WeatherDetail) as [ISJSON] FROM Weather;

[/sql]

isjson results

Nice. Both fields contain valid JSON.

Step 6 – OPENJSON

We will use the table-valued funtion OPENJSON to parse JSON text and return the JSON objects into the rows and columns we know and love. OPENJSON transforms the array of JSON objects into a table, in which each object is represented as one row, and key/value pairs are returned as cells. Source: https://msdn.microsoft.com/en-gb/library/dn921897.aspx

[sql]

— As you will see, this query displays the JSON data as an easy to read table.
DECLARE @json nvarchar(max) = (SELECT WeatherDetail FROM Weather where ID = 2)
SELECT *
FROM OPENJSON (@json,N’$.Forecasts’)
WITH
(
Location NVARCHAR(64) N’$.Forecast.Location’,
Date datetime N’$.Forecast.Date’,
WeatherDetails NVARCHAR(MAX) N’$.Forecast.WeatherDetails’
)

[/sql]

 

OPENJSON output

Step 7 – FOR JSON

Let’s now create a new table and insert some data so we can understand how to use FOR JSON.

[sql]

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
[Date] datetime,
WeatherDetail NVARCHAR(max) — No JSON data type
)

— Insert the data which we wil work with next
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 00:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 03:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 06:00:00.000′,’Heavy Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 09:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 12:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 15:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 18:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 21:00:00.000′,’Thunder’)
—
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 00:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 03:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 06:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 09:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 12:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 15:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 18:00:00.000′,’Windy’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 21:00:00.000′,’Windy’)

[/sql]

Step 8 FOR JSON AUTO

Use AUTO mode with the FOR JSON clause. (Formatted automatically based on structure of SELECT)

[sql]

— This select gives us our data in the familiar table format.
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]

— This select now formats our data as JSON
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON AUTO;

[/sql]

table data as json

Limitation: SSMS still uses the XML editor to show JSON so it doesn’t display it correctly when you click on it.

ssms still shows json as xml

Due to this, a good tool to Format your JSON into a readable format if needed can be found at https://jsonformatter.curiousconcept.com/

json data properly formatted

Step 9 – FOR JSON PATH

Use PATH mode with the FOR JSON clause to maintain control over format. The name value pairs are generated by using this syntax in the select: AS [Forecast.Location]. The root node is named by the value we pass in after the word root: ROOT(‘Forecasts’)

[sql]

SELECT
[Location] AS [Forecast.Location],
CAST([Date] as nvarchar) AS [Forecast.Date],
WeatherDetail as [Forecast.WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON PATH, ROOT(‘Forecasts’);

[/sql]

This is much nicer when we paste the output into https://jsonformatter.curiousconcept.com/.

Comparing to XML functions

[sql]

SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML AUTO;

SELECT
[Location] AS [Location],
CAST([Date] as nvarchar) AS [Date],
WeatherDetail as [WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML PATH (‘forecast’), ROOT(‘Forecasts’);

[/sql]

What’s next?

This blog post is quite large and still only covers a small part of JSON support for SQL Server 2016. There are more built in functions such as JSON_VALUE, JSON_QUERY, & JSON_MODIFY which I hope to cover soon.

Useful resources

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/07/bulk-importing-json-files-into-sql-server/
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/22/openjson-the-easiest-way-to-import-json-text-into-table/
https://msdn.microsoft.com/en-gb/library/dn921897.aspx
https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/
https://jsonformatter.curiousconcept.com/