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

Thinking about the need for DBAs and Developers to export data from the database to JSON strings, Microsoft created a variant of FOR XML to specifically address 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 for using JSON. The INCLUDE_NULL_VALUES parameter is used to include columns with a NULL value in the generated JSON string (by default, columns with a NULL value are not generated). The parameter WITHOUT_ARRAY_WRAPPER 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:

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:

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:

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:

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 selector '$ .databases' in the OPENJSON function to facilitate navigation in the elements without having to always inform 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:

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: