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 =
"tags":["Sport", "Water polo"]
Now, we can extract values and objects from the JSON text using the JSON_VALUE and JSON_QUERY functions:
JSON_VALUE(@json, '$.type') as type,
JSON_VALUE(@json, '$.info.address.town') as town,
JSON_QUERY(@json, '$.info.tags') as tags
The OPENJSON function enables you to reference some array in JSON text and return elements from that array:
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
FOR JSON PATH
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.