SQL Server: Presenting and Querying JSON Documents

Using the Database Engine, you can present data in either of the following ways:

  • Present JSON documents as relational data
  • Present relational data as JSON documents

The following sections describe these two methods. (Additionally, the last subsection describes how you can query JSON documents.)

1. Presenting JSON Documents as Relational Data

The Database Engine supports the OPENJSON function to present JSON documents as relational data. This function is a table-valued function that analyzes a given text to find an array of JSON objects. All objects found in the array are searched and, for each of them, the system generates a row in the output result. (For the description of table-valued functions, see Chapter 8).

There are two forms of the OPENJSON function:

  • With a predefined result schema
  • Without the schema

If a schema exists, it defines mapping rules that specify what properties will be mapped to the returned columns. Without such a schema, the result is a set of key-value pairs.

Example 29.3 shows the use of OPENJSON without the given schema.

Example 29.3

DECLARE @json NVARCHAR(MAX) =

N’ {“info”:{ “who”: “Fred” .”where”: “Microsoft” ,

  “friends”:[{“name”:”Lili”,”rank”:5}, {“name”: “Hank”, “rank”: 7}]}

SELECT [key], value FROM OPENJSON(@json, N’ $.info.friends’);

The result is

As you can see from the output of Example 29.3, the OPENJSON function searches in the array that is assigned to the @json variable. The variable is used as the first parameter of the OPENJSON function to return one row for each element in the array.

The second parameter of OPENJSON specifies the path, which is used to specify which part of the document will be displayed. (Examples with different path specifications will be given later in this chapter.)

The columns specified in the SELECT list of Example 29.3 define the output of key/value pairs discussed previously. By default, NULL is returned if the property is not found.

2. Presenting Relational Data as JSON Documents

If you want to convert the result set of a query to a JSON document, you can specify the FOR JSON clause at the end of your SELECT statement. In that case, the Database Engine takes the result, formats it as a JSON document, and returns it to the client. Every row is formatted as one object, with values generated as value objects and column names used as key names. The FOR JSON clause has two modes:

  • AUTO
  • PATH

The following subsections describe both modes.

2.1. AUTO Mode

With the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. Therefore, the user cannot change the output form.

AUTO mode returns the result set of a query as a simple, nested JSON tree. Each table in the FROM clause from which at least one column appears in the SELECT list is represented as an object. The columns in the SELECT list are mapped to the appropriate attributes of that object. Example 29.4 shows the use of AUTO mode.

Example 29.4

USE sample;

SELECT dept_no, dept_name

FROM department FOR JSON AUTO;

The result is

[{“dept_no”:”d1″,”dept_name”:”Research” }, 

{“dept_no”:”d2 “,”dept_name”:“Accounting “},

{“dept_no”:”d3 “,”dept_name”:”Marketing “}]

As you can see from the output of Example 29.4, the result is displayed as a JSON array.

2.2. PATH Mode

In PATH mode, column names or column aliases are treated as expressions that indicate how the values are being mapped to JSON. (An expression consists of a sequence of nodes, separated by /. For each slash, the system creates another level of hierarchy in the resulting document.) Example 29.5 shows the use of PATH mode.

NOTE In contrast to AUTO mode, using PATH mode allows you to maintain full control over the format of the JSON output.

Example 29.5

USE sample;

SELECT dept_no AS [Department.Number], dept_name AS [Department.Name]

FROM department FOR JSON PATH, ROOT (‘Departments’);

The result is

{“Departments”:

[{“Department”:{“Number”:”d1 “,”Name”:”Research”}},

{“Department”: {“Number”:”d2 “,”Name”:”Accounting “}},

{“Department”:{“Number”:”d3 “, “Name”:”Marketing “}}]}

To extend the user’s control over the output for the PATH mode, the Database Engine supports two options:

  • ROOT
  • INCLUDE_NULL_VALUES

The ROOT option allows you to add a single, top-level element to the JSON output of the FOR JSON clause. Example 29.5 shows how the output of the corresponding query can be extended with the top-level element called “Departments.”

Generally, the output does not include JSON properties for NULL values in the result of a query. To include NULLs, you have to specify the INCLUDE_NULL_VALUES option.

2. Querying JSON Documents

The Database Engine supports three functions that are used to query JSON documents:

  • isjson
  • json_value
  • json_query

The following subsections describe these functions.

2.1. isjson

The isjson function tests whether a string contains a valid JSON document. This function is usually used to create a constraint that checks whether the document is well formed or not. The syntax of this function is

isjson(expression)

where expression is the name of a variable or a column that contains JSON text.

Example 29.6 shows the use of this function.

Example 29.6

USE sample;

SELECT id, person_and_friends

FROM json_table

WHERE isjson(person_and_friends) > 0;

The query in Example 29.6 tests whether the JSON documents stored in the table json_table are valid. (The function returns 1 if the string contains a valid JSON document; otherwise, it returns 0.) After that, it displays all valid documents. In this case, all values are valid and will be displayed.

2.2. json_value

The json_value function extracts a scalar value from a JSON string. The syntax of this function is

json_value(expression, path)

where expression is the name of a variable or a column that contains JSON text and path is a JSON path that specifies the property to extract.

Example 29.7 shows the use of this function.

Example 29.7

USE sample;

SELECT id,json_value(person_and_friends,’$.info.where’) AS company

FROM json_table

WHERE isjson(person_and_friends) > 0

AND json_value(person_and_friends, ‘$.info.who’) = ‘Fred’; 

The result is

Example 29.7 uses two json_value functions and an isjson function. The latter is used to test whether the JSON documents stored in the person_and_friends column are valid. The second json_value function searches for such objects where the name/value pair is equal to “who/Fred.” After that, the first json_value function displays the value of the corresponding company, which is stored in the key name called where.

Path expressions, such as ‘$.info.who’ and ‘$.info.where’ in Example 29.7, are explained in the next subsection.

JSON Path Expressions JSON path expressions are used to reference the properties of JSON objects. These expressions are a part of the OPENJSON, json_value, and json_query functions. Each path contains a set of path steps. Path steps can contain key names, array elements, or the dot operator, which indicates a member of an object.

The following list shows some examples of path expressions:

  • $ The entire document
  • $.info[0].who Fred
  • $.info[1] “who”: “Tom”, “where”: “IBM”, “friends”: [ { “name”:

“Sharon”, “rank”: 2}, {“name”: “Monty”, “rank”: 3} ] }}’);

2.3. json_query

This function returns extracts of an object or an array from a JSON string. The result is of type NVARCHAR(MAX). The syntax is analogous to the syntax of the json_value function. Example 29.8 shows the use of the json_query function.

Example 29.8

USE sample;

SELECT id, person_and_friends,

json_query(person_and_friends, ‘$.info.where’)

FROM json_table;

The query in Example 29.8 returns the entire JSON document.

The key difference between json_value and json_query is that json_value returns a scalar value, while json_query returns an object or an array. For instance, if the path is specified as $.info[0].who, the result (Fred) will be displayed when the json_value function is applied. For the same path expression, json_query displays NULL or error. On the other hand, if the path is specified as $, the result (the entire document) will be displayed when json_query is applied. In the case of json_value, NULL or error will be displayed.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *