JSON Support in SQL Server

Printer-friendly version

JavaScript Object Notation (JSON) support and a long-awaited feature of SQL Server has been added to the 2016 version, which will allow you to exchange JSON data between applications and SQL Server, parse JSON formatted data so it can be stored in a relational format and convert relational data into JSON formatted data. 

Microsoft has added some new functions to provide support for querying JSON data stored in SQL Server (ISJSON, JSON_VALUE and JSON_QUERY) as well as OPENJSON and FOR JSON, see Figure 1.

  • ISJSON - Test whether a string contains valid JSON
  • JSON_VALUE - Extracts a scalar value from a JSON string
  • JSON_QUERY - Extracts an object or an array

 

Figure 1. JSON functions in SQL Server (source: MSDN)

As Jovan points out, if you have JSON text, you can extract data from JSON or verify that JSON is properly formatted using built-in functions JSON_VALUE, JSON_QUERY and ISJSON. For more advanced querying and analysis, the OPENJSON function can transform an array of JSON objects into a set of rows. Any SQL query can be executed on the returned result set. Finally, there is the FOR JSON clause that enables you to format query results as JSON text.

We can start with a simple example. In the following Transact-SQL code, we will define a text variable where we will put some JSON text:

DECLARE @json NVARCHAR(4000)

SET @json = 

N'{

"info":{  

"type":1,

"address":{  

"town":"Bristol",

"county":"Avon",

"country":"England"

},

"tags":["Sport", "Water polo"]

},

"type":"Basic"

}'

Now, we can extract values and objects from the JSON text using the JSON_VALUE and JSON_QUERY functions:

SELECT  

JSON_VALUE(@json, '$.type') as type,

JSON_VALUE(@json, '$.info.address.town') as town,

JSON_QUERY(@json, '$.info.tags') as tags

This query will return “Basic,” “Bristol” and ["Sport", "Water polo"] values. The JSON_VALUE function returns one scalar value from JSON text (e.g. strings, numbers, true/false) that is placed on a JSON path specified as the second parameter. JSON_QUERY returns an object or array (in this example an array of tags) on the JSON path. JSON built-in functions use JavaScript-like syntax to reference values and objects in JSON text via second parameter.

The OPENJSON function enables you to reference some array in JSON text and return elements from that array:

SELECT value

FROM OPENJSON(@json, '$.info.tags')

In this example, string values from the tags array are returned. However, the OPENJSON function can return any complex object.

Finally, there is a FOR JSON clause that can format any result set returned by a SQL query as JSON text:

SELECT object_id, name

FROM sys.tables

FOR JSON PATH

Be sure to check out his other posts, JSON Data or learn more in the SQL Server 2016 blog post series.

 

About the Author:

TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.

Comments (0)

Related Blogs

October is not just about pumpkins, fall foliage, and cooler temps anymore. October 2018 also means the exciting introduction of Microsoft Dynamics 365 for Customer Engagement.

Back in 2016, Microsoft introduced its intentions to refresh its CRM and ERP strategy with Dynamics 365. At the heart of its services was the Common Data Model (CDM).

Have you noticed how the same cliche questions always get asked at job interviews — over and over again?

Microsoft Ignite took place September 24-28, 2018 in Orlando, Florida.