SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

Views: 2.033
Reading Time: 12 minutes

Hello people,
All right with you ?

In this post I will demonstrate how to use native SQL Server database support for JSON, a document standard widely used in integrations and Webservices today. This feature is available from the 2016 version of SQL Server and is intended to allow you to export database data to a JSON format string and read and manipulate JSON format data.

If you are using a version earlier than SQL Server 2016, ie without native JSON support, but still need to work with JSON strings, read my post Reading JSON Strings, Importing to Database, and Exporting to XML in SQL Server and know how to do that.

If you need to import / export JSON strings from / to text files physically, check out these two posts:

Exporting database data to a JSON string

View content
Thinking about the need for DBA's and Developers to export database data to JSON strings, Microsoft has created a FOR XML variant to deal specifically with JSON, which is FOR JSON.

Its syntax is very similar to XML, so if you already know how to manipulate XML through SQL Server, you will already be well acquainted with this new 2016 feature. If you do not know, see more accessing the post SQL Server - How to read, import, and export data from XML files.

1 Example

In this first example, I will use JSON's default mode, which is auto. It will simply generate JSON according to the data entered without making any changes to its structure.

Original table:

JSON generated:

2 Example

Now in this example, I will use the ROOT parameter to define a root element for my JSON string and I will also change the field titles.

Original table:

JSON generated:

3 Example

In this example, I will demonstrate 2 options to use with JSON. The INCLUDE_NULL_VALUES parameter serves to include NULL columns in the generated JSON string (by default, NULL columns are not generated). The WITHOUT_ARRAY_WRAPPER parameter serves to remove the characters “[” and “]” from the generated JSON string.

Result:

4 Example

In this example, I will demonstrate how to export data from a table and store the generated JSON string in a variable.

Result:

5 Example

In this last example, I will demonstrate how to completely control the structure of your JSON string using the PATH parameter.

JSON generated:

Manipulating data in JSON strings with JSON_VALUE

View content
Another nice feature for JSON string manipulation is the JSON_VALUE function, which allows you to manipulate JSON strings and return desired information from a JSON string.

1 Example

In this first example, I will demonstrate how to extract information quickly using the JSON_VALUE function.

Result:

2 Example

In this example, I will demonstrate how to read data from JSON strings that have multivalued data (arrays) and the reading will be done using array indexes.

Result:

3 Example

In this example, I will demonstrate how to use JSON_VALUE using a string embedded in the command itself.

Result:

4 Example

Finally, in this example I will demonstrate how to use the JSON_VALUE function as a computed column, where I insert a JSON string into the table, and the data is automatically calculated for me.

Result:

Manipulating data in JSON strings with JSON_QUERY

View content
After learning how to use JSON_VALUE to work with scalar information, I will demonstrate how to use the JSON_QUERY function to return JSON value objects and arrays.

1 Example

In this first example, I will demonstrate some basic uses of the JSON_QUERY function.

Result:

2 Example

In this example, I will demonstrate how to return arrays using N-indices from a JSON string.

Result:

3 Example

In this example, I will make some comparisons between JSON_QUERY and JSON_VALUE, to demonstrate the difference of 2 functions.

Result:

Validating JSON Strings with the ISJSON Function

View content
A very useful function for working with JSON is ISJSON, which allows you to check if a string is a JSON string that conforms to recognized JSON standards that can be read by SQL Server.

I will demonstrate some examples for you to easily understand how to use this function.

1 Example

Result:
1 (valid JSON)

2 Example

In this example, I will use exactly the same JSON as the previous example, but I will remove a bracket “]” from the JSON string so that it is no longer valid.

Result:
0 (invalid JSON)

3 Example

Finally, I will demonstrate some quick examples of JSON validation.

Result:

Importing JSON to table with OPENJSON

View content
Using the OPENJSON function, we can easily import data from a JSON string into a SQL Server table. Note that in this example, JSON has a root element (databases). Therefore, I will use the '$ .databases' selector in the OPENJSON function to make it easier to navigate the elements without having to always enter the root element.

1 Example

Result:

2 Example

In this example, I will demonstrate how to import another JSON, this time without root element, and will convert the date to DATETIME.

Result:

3 Example

In this example, I will demonstrate how to import data from a multilevel JSON string to table.

Result:

4 Example

In this example, I will demonstrate how to import data from a multilevel JSON string, returning only the listing, to table. In this scenario, I will have to use CROSS APPLY between JSON nodes to traverse it.

Result:

5 Example

In this last example, I will demonstrate again how to import data from a multilevel JSON string, returning only the listing, to table. In this scenario, I will have to use CROSS APPLY between JSON nodes to traverse it.

Result:

Modifying JSON Strings with JSON_MODIFY

View content
Using the JSON_MODIFY function, you can easily change the data of a JSON string and work with it later as needed.

Example of use

Result:

That's it folks!
I hope you enjoyed this post.
To the next.