JSON (Extract & Query)

A new feature in SQL Server 2016 (also available in Azure SQL database) is the ability to create and query  JSON (Javascript object notation) documents, which have now become a common alternative to XML.

Lets look at some examples, I’ll be using tables from the new sample database for SQL Server 2016 WorldWideImporters which you can download from this link

Creating JSON using T-SQL

To create a JSON document its quite simple, simply create your SQL Statement to extract the data which you require, then you need to append WITH JSON [AUTO or PATH] at the bottom of the query.

Whether you use AUTO or PATH depends on what type of output you required the differences are as follows:

  • Use PATH mode with the FOR JSON clause. When you use PATH mode with the FOR JSON clause, you maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.
  • Use AUTO mode with the FOR JSON clause. When you use AUTO mode with the FOR JSON clause, the JSON output is formatted automatically based on the structure of the SELECT statement

For this example we’ll be using the AUTO option, if we run the following query to extract some data from the invoices table

SELECT TOP 1 CustomerID,CustomerPurchaseOrderNumber , Deliveryinstructions
FROM Sales.Invoices
WHERE CustomerID = 832 AND CustomerPurchaseOrderNumber = 12126
FOR JSON AUTO

 

We will receive the following output in JSON

[{“CustomerID”:832,”CustomerPurchaseOrderNumber”:”12126″,”Deliveryinstructions”:”Suite 24, 1345 Jun Avenue”}]

Querying JSON using T-SQL

We’re also able to query JSON documents which are store in the database, JSON doesn’t have a native datatype like XML has, but JSON documents can be stored in data type NVARCHAR(MAX)

There are three commands which can be used.

  • Use the JSON_VALUE function to extract a scalar value from a JSON string
  • Use JSON_QUERY to extract an object or an array
  • Use the ISJSON function to test whether a string contains valid JSON.

In the below query we’ll use the three commands to extract the data from a JSON column in the Application.People table (CustomerFields) of the WorldWideImporters database.

To explain the below query more, we’re using the JSON_QUERY function to extract the other languages data as there is more than one in an list, but then we only need to use the JSON_VALUE function for the hire data and the title as these are single scalar values, you’ll also notice that we’re using the ISJSON function to ensure the data in the CustomFields column is formatted as JSON.

 SELECT
JSON_QUERY(CustomFields,'$.OtherLanguages') as OtherLanguages,
JSON_VALUE(CustomFields,'$.HireDate') as HireDate,
JSON_VALUE(CustomFields,'$.Title') as Title
FROM [WideWorldImporters].[Application].[People]
WHERE ISJSON(CustomFields) = 1 AND PersonID = 10

Now if we look a the query results we get the following

1

You can see we’re now got this data in a relational format.

Have a play around as clearly it’s more powerful than this quick example I knocked together 🙂

 

 

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s