Working with JSON documents

JSON documents that are stored in Db2® tables can be retrieved by using normal SQL query statements.

To access the contents of a JSON document by using SQL, you can use one of the following functions: JSON_QUERY, JSON_TABLE, or JSON_VALUE.

All of these functions take a JSON or BSON document as input. The JSON_QUERY scalar function returns the JSON value that matches the specified JSON key. The JSON_VALUE scalar function returns the SQL data type representation of the JSON value matching the specified JSON key. The JSON_TABLE table function returns a relational result set with SQL data type representations of the matching JSON data.

You can use JSON_VALUE and JSON_QUERY to search JSON documents in a table for a specific value. For example, where the JSON documents in the EMP_DATA column are queried to find the last name of all employees:

SELECT JSON_VALUE(EMP_DATA, 'strict $.lastname' RETURNING CHAR(50)) AS LASTNAME FROM JSON_EMP

JSON_TABLE can be used to provide simple access to multiple elements within a JSON document as a single result set as shown in this example:

SELECT "mgrno", "deptno","deptname" FROM JSON_DEPT D, JSON_TABLE(D.DEPT_DATA, 'strict $' COLUMNS("mgrno" CHAR(6) , 
"deptno" CHAR(3),"deptname" VARCHAR(40)) ERROR ON ERROR)

JSON_TABLE can also be used to integrate the data within JSON documents with other data, using standard SQL statements. The other data can be JSON and non-JSON format, The following example shows a join between two tables, both containing the individual employee and department details within JSON documents, to find the manager name for each department:

WITH DEPTS AS (SELECT "mgrno", "deptno","deptname"
               FROM JSON_DEPT D,
                    JSON_TABLE(D.DEPT_DATA, 'strict $' COLUMNS("mgrno" CHAR(6) , "deptno" CHAR(3),"deptname" VARCHAR(40)) ERROR ON ERROR)),
     EMPS AS (SELECT "empno", "lastname", "firstnme" FROM JSON_EMP E, JSON_TABLE(E.EMP_DATA, 'strict $' COLUMNS("empno" CHAR(6) ,"firstnme" 
VARCHAR(30), "lastname" VARCHAR(30)) ERROR ON ERROR))
SELECT "deptno","deptname", "lastname", "firstnme"
FROM DEPTS, EMPS
WHERE EMPS."empno" = DEPTS."mgrno"

Using JSON_VALUE also allows you to now integrate JSON documents with other data by using SQL. For example, looking for the manager of the department where the employee with the employee number 29863 is assigned:

SELECT D.MANAGER
FROM DEPT D, JSON_EMP J
WHERE JSON_VALUE(J.EMP_DATA, 'strict $.empno' RETURNING INTEGER) = 29863
AND D.DEPTNO = JSON_VALUE(J.EMP_DATA, 'strict $.deptno' RETURNING INTEGER)

Similarly, JSON_TABLE now allows you to apply other SQL functions to JSON data or to join between the content of JSON documents and other relational data.

SQL/JSON path expression

In order to guide Db2 processing of a JSON document for the JSON_VALUE, JSON_TABLE, and JSON_QUERY functions, the user must pass information to them on how to navigate the JSON document to find the wanted values. This information is passed as input to relevant JSON SQL routines in the form of an SQL/JSON path expression.

The SQL/JSON path expression defines the access path that is used by Db2 when processing the JSON document. This path expression consists of a path mode and an accessor expression. The path mode specifies whether some types of discrepancies between the accessor expression and the structure of the JSON data are tolerated (lax mode) or not (strict mode). The accessor expression provides a description of the starting point for the JSON SQL function processing within the JSON data.

See sql-json-path-expression for more information.

Interaction of ON EMPTY and ON ERROR clauses

The JSON_TABLE, JSON_QUERY, and JSON_VALUE SQL functions all provide the ability to specify a behaviour to be followed by Db2 when the provided SQL/PATH expression does not return anything from the JSON document, the ON EMPTY clause, or when an error is encountered during the processing of the JSON document, the ON ERROR clause. When the ERROR option is specified for the ON EMPTY clause, the behaviour specified for the ON ERROR clause will be followed. This means that an error will only be returned as a result of an empty path result if the ERROR option is also specified for the ON ERROR clause.

Additionally, for the JSON_TABLE table function, the behaviour specified for the ON ERROR clause available at the column level of the statement specification is overridden by the behaviour specified for the ON ERROR clause available at the higher, table level.

Indexing

While tuning queries by using the JSON SQL routines, you might find that creating indexes will help queries perform better. In these cases, you can choose one of these approaches to create an index:

Create an expression-based index by using JSON_VALUE to pull the key values from of the JSON document.
Use JSON_VALUE to extract the values needed for the index from the JSON document into separate columns and then create indexes on these separate columns. Note: This approach assumes that the chosen values are not later updated in the JSON document; if they are, you will need to make identical changes to the separate columns as well in order to have the index reflect the update to the JSON document.

For example, an expression-based index created by using JSON_VALUE. The index is created by using JSON_VALUE to find the value of the empno JSON key from the JSON document that is stored in the EMP_DATA column of the JSON_EMP table:

CREATE INDEX IX1 ON JSON_EMP(JSON_VALUE(EMP_DATA, 'strict $.empno' RETURNING INTEGER));