jump to navigation

Built-in support for JSON in SQL Server 2016 November 20, 2016

Posted by fofo in Sql Server, SQL Server 2016.
Tags: ,
trackback

This is going to be the first post in a series of posts about the new features of SQL Server 2016.

SQL Server 2016 was officially released back in June 2016.

There are many new features in SQL Server 2016 and in  my opinion the most important are:

  • Always Encrypted
  • Query Store
  • Row Level Security
  • Temporal Tables
  • Stretch Database
  • Dynamic Data Masking
  • PolyBase
  • JSON Support

I am going to discuss the JSON support in SQL Server 2016 in this post.

I have installed SQL Server 2016 Enterprise edition in my machine but you can use the SQL Server 2016 Developer edition as well. You can download SQL Server 2016 Developer edition for free.

You can download the SQL Server 2016 Developer edition by joining the Visual Studio Dev Essentials which is free and simple. After joining you will be able to download SQL Server 2016 developer edition in your machine.

JSON is an acronym for JavaScript Object Notation, and it’s a lightweight format for exchanging data between different source types, similar to how XML is used.

The transferred text is easy to read, and the objects consist of attribute-value pairs.

Let’s look at a simple  JSON example

 

{“doctors”:[
{“firstName”:”John”, “lastName”:”Smith”},
{“firstName”:”Anna”, “lastName”:”Bernard”},
{“firstName”:”Peter”, “lastName”:”Feller”}
]}

SQL developers think that the SQL team has provided a  built-in JSON data type. That is not true.

JSON text is typically stored in varchar or nvarchar columns, and is indexed as plain text.

SQL Server 2016 provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

Hence, we can format SQL Server data or query output as JSON by adding the FOR JSON clause to a select statement.

On the other hand we can convert JSON to data that feeds to a table from our database. This is done using the OPENJSON function.

Let’s us start with our first example.

In all my examples I will be using the AdventureWorks 2016 database. You can download it here.

We will convert the query results against our database to JSON format using the FOR JSON clause.

We will create a SELECT statement from a table and then transform it to JSON.

Open a new query window. Type (copy-paste the following) in that query window. Execute the query.

USE AdventureWorks2016

SELECT Production.Product.Name ,
 Production.Product.StandardCost ,
 Production.Product.ListPrice ,
 Production.Product.Size
FROM Production.Product
WHERE ListPrice > 1800
FOR JSON AUTO

 

I copy and paste the output of the query to online JSON parser – http://jsonviewer.stack.hu/

This is the output after formatting the JSON output.

[
{
“Name”: “Road-150 Red, 62”,
“StandardCost”: 2171.2942,
“ListPrice”: 3578.2700,
“Size”: “62”
},
{
“Name”: “Road-150 Red, 44”,
“StandardCost”: 2171.2942,
“ListPrice”: 3578.2700,
“Size”: “44”
},
{
“Name”: “Road-150 Red, 48”,
“StandardCost”: 2171.2942,
“ListPrice”: 3578.2700,
“Size”: “48”
},
{
“Name”: “Road-150 Red, 52”,
“StandardCost”: 2171.2942,
“ListPrice”: 3578.2700,
“Size”: “52”
},
{
“Name”: “Road-150 Red, 56”,
“StandardCost”: 2171.2942,
“ListPrice”: 3578.2700,
“Size”: “56”
},
{
“Name”: “Mountain-100 Silver, 38”,
“StandardCost”: 1912.1544,
“ListPrice”: 3399.9900,
“Size”: “38”
},
{
“Name”: “Mountain-100 Silver, 42”,
“StandardCost”: 1912.1544,
“ListPrice”: 3399.9900,
“Size”: “42”
},
{
“Name”: “Mountain-100 Silver, 44”,
“StandardCost”: 1912.1544,
“ListPrice”: 3399.9900,
“Size”: “44”
},
{
“Name”: “Mountain-100 Silver, 48”,
“StandardCost”: 1912.1544,
“ListPrice”: 3399.9900,
“Size”: “48”
},
{
“Name”: “Mountain-100 Black, 38”,
“StandardCost”: 1898.0944,
“ListPrice”: 3374.9900,
“Size”: “38”
},
{
“Name”: “Mountain-100 Black, 42”,
“StandardCost”: 1898.0944,
“ListPrice”: 3374.9900,
“Size”: “42”
},
{
“Name”: “Mountain-100 Black, 44”,
“StandardCost”: 1898.0944,
“ListPrice”: 3374.9900,
“Size”: “44”
},
{
“Name”: “Mountain-100 Black, 48”,
“StandardCost”: 1898.0944,
“ListPrice”: 3374.9900,
“Size”: “48”
},
{
“Name”: “Mountain-200 Silver, 38”,
“StandardCost”: 1265.6195,
“ListPrice”: 2319.9900,
“Size”: “38”
},
{
“Name”: “Mountain-200 Silver, 42”,
“StandardCost”: 1265.6195,
“ListPrice”: 2319.9900,
“Size”: “42”
},
{
“Name”: “Mountain-200 Silver, 46”,
“StandardCost”: 1265.6195,
“ListPrice”: 2319.9900,
“Size”: “46”
},
{
“Name”: “Mountain-200 Black, 38”,
“StandardCost”: 1251.9813,
“ListPrice”: 2294.9900,
“Size”: “38”
},
{
“Name”: “Mountain-200 Black, 42”,
“StandardCost”: 1251.9813,
“ListPrice”: 2294.9900,
“Size”: “42”
},
{
“Name”: “Mountain-200 Black, 46”,
“StandardCost”: 1251.9813,
“ListPrice”: 2294.9900,
“Size”: “46”
},
{
“Name”: “Road-250 Red, 44”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “44”
},
{
“Name”: “Road-250 Red, 48”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “48”
},
{
“Name”: “Road-250 Red, 52”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “52”
},
{
“Name”: “Road-250 Red, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”
},
{
“Name”: “Road-250 Black, 44”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “44”
},
{
“Name”: “Road-250 Black, 48”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “48”
},
{
“Name”: “Road-250 Black, 52”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “52”
},
{
“Name”: “Road-250 Black, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”
},
{
“Name”: “Touring-1000 Yellow, 46”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “46”
},
{
“Name”: “Touring-1000 Yellow, 50”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “50”
},
{
“Name”: “Touring-1000 Yellow, 54”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “54”
},
{
“Name”: “Touring-1000 Yellow, 60”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “60”
},
{
“Name”: “Touring-1000 Blue, 46”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “46”
},
{
“Name”: “Touring-1000 Blue, 50”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “50”
},
{
“Name”: “Touring-1000 Blue, 54”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “54”
},
{
“Name”: “Touring-1000 Blue, 60”,
“StandardCost”: 1481.9379,
“ListPrice”: 2384.0700,
“Size”: “60”
}
]

In AUTO mode, the structure of the SELECT statement determines the format of the JSON output. If we want to explicitly specify the format of the output, we can use the PATH mode instead of AUTO.

Open a new query window. Type (copy-paste the following) in that query window. Execute the query.

USE AdventureWorks2016

--AUTO
SELECT Production.Product.Name ,
 Production.Product.StandardCost ,
 Production.Product.ListPrice ,
 Production.Product.Size ,
 Production.ProductModel.Name AS modelname
FROM Production.Product
 INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE ListPrice > 1800
 AND ProductModel.Name IN ( 'Road-250' )
 FOR JSON AUTO

--PATH

SELECT Production.Product.Name ,
 Production.Product.StandardCost ,
 Production.Product.ListPrice ,
 Production.Product.Size ,
 Production.ProductModel.Name AS modelname
FROM Production.Product
 INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE ListPrice > 922
 AND ProductModel.Name IN ( 'Road-250' )
 FOR JSON PATH

GO
 

This is the output of the AUTO statement.

[
{
“Name”: “Road-250 Red, 44”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “44”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Red, 48”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “48”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Red, 52”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “52”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Red, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Black, 44”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “44”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Black, 48”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “48”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Black, 52”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “52”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
},
{
“Name”: “Road-250 Black, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“Production.ProductModel”: [
{
“modelname”: “Road-250”
}
]
}
]

This is the output of the PATH statement

[
{
“Name”: “Road-250 Red, 44”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “44”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 48”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “48”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 52”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “52”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 44”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “44”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 48”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “48”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 52”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “52”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“modelname”: “Road-250”
}
]

Now let’s see how we can use the ROOT statement.

Open a new query window. Type (copy-paste the following) in that query window. Execute the query.

USE AdventureWorks2016

SELECT Production.Product.Name ,
 Production.Product.StandardCost ,
 Production.Product.ListPrice ,
 Production.Product.Size ,
 Production.ProductModel.Name AS modelname
FROM Production.Product
 INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE ListPrice > 1800
 AND ProductModel.Name IN ( 'Road-250' )
 FOR JSON PATH,ROOT ('Production.Product')

This is the output of the JSON result after formatting.

{
“Production.Product”: [
{
“Name”: “Road-250 Red, 44”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “44”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 48”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “48”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 52”,
“StandardCost”: 1518.7864,
“ListPrice”: 2443.3500,
“Size”: “52”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Red, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 44”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “44”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 48”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “48”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 52”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “52”,
“modelname”: “Road-250”
},
{
“Name”: “Road-250 Black, 58”,
“StandardCost”: 1554.9479,
“ListPrice”: 2443.3500,
“Size”: “58”,
“modelname”: “Road-250”
}
]
}

We wanted to specify a root element for our data format and we used the ROOT keyword.

The nodes returned by our query are children of the root node Production.Product

We can also convert JSON data to rows and columns.

We can use the OPENJSON function.  There are two ways of calling OPENJSON. We can use it with a default schema. In this case, the function returns a table with one row for each property of the object or for each element of the array.

Open a new query window. Type (copy-paste the following) in that query window. Execute the query.


DECLARE @json NVARCHAR(MAX) = N'
{
 "Name":"Road-155,62",
 "StandardCost":2123,
 "ListPrice":3123,
 "Size":62

}';
 
SELECT * FROM OPENJSON(@json);

The output of this statement is

key value type
Name Road-155,62 1
StandardCost 2123 2
ListPrice 3123 2
Size 62 2

 

As you can see we can the OPENJSON  function returns three values:

key: Property name within the object or index of the element within the array.
value: Property value within the object or value of the array element specified by the index.
type: Value’s data type, represented numerically, as described in the following table:

Numeric Value Data Type
0 null
1 string
2 int
3 true or false
4 array
5 object

We can also use the OPENJSON function with an explicit schema, and in this case the function returns a table with the schema that we define in the WITH clause.

In the WITH clause, we specify the output columns, their types, and the paths of the source properties for each column.

Open a new query window. Type (copy-paste the following) in that query window. Execute the query.


DECLARE @JSON NVARCHAR(max);
SET @JSON =N'{"Products"
{"Production.Product": 
 [
 {
 "Name": "Road-250 Red, 44",
 "StandardCost": 1518.7864,
 "ListPrice": 2443.3500,
 "Size": "44",
 "ProductModel": 
 {
 "modelname": "Road-250"
 }
 
 },
 {
 "Name": "Road-250 Red, 48",
 "StandardCost": 1518.7864,
 "ListPrice": 2443.3500,
 "Size": "48",
 "ProductModel": 
 {
 "modelname": "Road-250"
 }
 }
 ]
 }
 }'

 select * from OPENJSON (@JSON, '$.Products.Production.Product')

 with ( Name nvarchar(50) '$.Name',
 StandardCost decimal(18,2) '$.StandardCost',
 Listprice decimal(18,2) '$.ListPrice',
 Size int '$.Size',
 modelname nvarchar(100) '$.ProductModel.modelname'
 )

 

We can see that Name,StandardCost,ListPrice,Size come directly under Production.Product, while the modelname has another parent, ProductModel, so when we mention them we also specify the ProductModel node.

Finally let’s have a look at the ISJSON  built-in function. This function validates the JSON text so we can find out whether a string contains a valid JSON.

In this final example I make a TABLE variable in which a column is going to store string values that will hold date formatted as JSON. I am populating the TABLE variable with two rows.

By executing the query below we see that returns 2 rows because our JSON format is valid.

This is a good function for validating strings that are supposed to have a JSON format before exchanging them with client applications

 
DECLARE @temp TABLE
 (
 id INT ,
 value NVARCHAR(MAX)
 )

INSERT INTO @temp
VALUES ( 1, '{
 "Name": "Road-150 Red, 62",
 "StandardCost": 2171.2942,
 "ListPrice": 3578.2700,
 "Size": "62"
 }' ), ( 2, '{
 "Name": "Road-150 Red, 44",
 "StandardCost": 2171.2942,
 "ListPrice": 3578.2700,
 "Size": "44"
 }' )

 SELECT * FROM @temp
 where ISJSON([Value]) > 0 

JSON support in SQL Server is great and a much anticipated feature.

Hope it helps!!!

Comments»

1. Drop if Exists T-SQL Enhancement statement in SQL Server 2016 | DOT NET RULES - December 23, 2016

[…] This is the fourth post in a series of posts about the new features of SQL Server 2016. You can find the first post in the SQL Server 2016 (JSON support in SQL Server 2016) series here. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: