Basics Of Working With JSON In SQL Server

Fig. 2 Returning a ResultSet in JSON Format

Fig. 6 ResultSet from Listing 5

Fig. 7 ResultSet from Querying HR.Employees

The ISJSON function performs a simple test to confirm whether a text document is represented in a valid JSON format. Listing 6 shows two ways of using this function to test a JSON document. By making one small change in the JSON document, we can get SQL Server to return a 0 (meaning: the document is NOT JSON) when we run this query. Just for fun, I will let you figure out the small change I made to the JSON object (see Fig. 8a and 8b).

Fig. 8a IS JSON/8b IS NOT JSON

It is worth mentioning, that using web sites such as https://jsonformatter.curiousconcept.com you can quickly validate JSON text or format prepared text as JSON.

In order to demonstrate the use of the functions JSON_VALUE, JSON_QUERY, and JSON_MODIFY, we created a table with a JSON column using the code in Listing 7. Note that the type for the column in question is a regular string data type NVACHAR(MAX). SQL Server does not have a special data type for JSON data in relational tables.

JSON_VALUE and JSON_QUERY appear similar but are different in the sense that while JSON_VALUE extracts scalar values from a JSON text, JSON_QUERY extracts objects or arrays. In other words, you are likely to get an error or a NULL if you try to extract a scalar value from a JSON text using JSON_QUERY. JSON_MODIFY allows you to change a specific value within JSON text that is stored within a column in a relational table. Listing 8 shows simple examples of using the JSON_* functions. While trying this out you will observe that the JSON path name is case sensitive. Microsoft documentation shows more examples of use cases for these functions.