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.

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 index key values from within the JSON document and place these values into other columns or tables so that appropriate indexes can be created on the non-JSON columns.

Note: Both of these approaches assume that the values within the JSON document do not change once the index is created.

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));