JSON_TABLE table function

The JSON_TABLE table function returns a result table from the evaluation of SQL/JSON path expressions. Each item in the result sequence of the row SQL/JSON path expression represents one or more rows in the result table.

Read syntax diagramSkip visual syntax diagramJSON_TABLE(JSON-expression FORMAT JSONFORMAT BSON,'strict $'COLUMNS(,json-table-regular-column-definitionjson-table-formatted-column-definition)ERROR ON ERROR)
json-table-regular-column-definition
Read syntax diagramSkip visual syntax diagramcolumn-namedata-typePATHcolumn-path-expression-constantNULL ON EMPTYERRORDEFAULTdefault-expressionON EMPTYNULLERRORDEFAULTdefault-expressionON ERROR
json-table-formatted-column-definition
Read syntax diagramSkip visual syntax diagramcolumn-namedata-typeFORMAT JSONPATHcolumn-path-expression-constantWITHOUTARRAYWRAPPERWITHUNCONDITIONALCONDITIONALARRAYWRAPPERKEEP QUOTESON SCALAR STRINGOMIT QUOTESON SCALAR STRINGNULL ON EMPTYERROREMPTY ARRAYEMPTY OBJECTON EMPTYNULLERROREMPTY ARRAYEMPTY OBJECTON ERROR
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)

The schema is SYSIBM. The function name cannot be specified as a qualified name.

JSON-expression
An expression that returns a value that is a built-in string data type, except the following data types (SQLSTATE 42815):
  • GRAPHIC
  • VARGRAPHIC
  • DBCLOB
  • BINARY
  • CHAR FOR BIT DATA
  • VARCHAR FOR BIT DATA
  • A user-defined type that is sourced on any of the previously listed data types

If a character value is returned, it must contain correctly formatted JSON data (SQLSTATE 22032). If a binary data type is returned, it is interpreted according to the explicit or implicit FORMAT clause.

FORMAT JSON
JSON-expression is formatted as JSON data.

If JSON-expression is a character string data type, it is treated as JSON data.

If JSON-expression is a binary string data type, it is interpreted as UTF-8 data.

FORMAT BSON
Specifies that JSON-expression is formatted as the BSON representation of JSON data (SQLSTATE 22032). JSON-expression must be a binary string data type (SQLSTATE 42815).
'strict $'
Specifies that an error is reported when the specified path expression cannot be used to navigate the current JSON document from the start of the context item. The error is handled according to the current ON ERROR clause.
COLUMNS
Specifies the output columns of the result table, including the column name, data type, and how the column value is computed for each row. The sum of the result column lengths cannot exceed 64 KB.
json-table-regular-column-definition
Specifies an output column of the result table, including the column name, data type, and an SQL/JSON path expression to extract the value from the sequence item for the row.
column-name
Specifies the name of the column in the result table. The name cannot be qualified and the same name cannot be used for more than one column of the result table (SQLSTATE 42711).
data-type
Specifies the data type of the column.

See CREATE TABLE statement for the description of built-in data types.

PATH column-path-expression-constant
Specifies a character string constant that is interpreted as an SQL/JSON path. The column-path-expression-constant specifies an SQL/JSON path expression that determines the column value regarding an item that is the result of evaluating the SQL/JSON path expression in sql-json-path-expression.

For more information about the content of an SQL/JSON path expression, see sql-json-path-expression.

Given an item from the result of processing the sql-json-path-expression as the externally provided context item, the column-path-expression-constant is evaluated and returns an output sequence. The column value is determined based on this output sequence as follows:

  • If an empty sequence is returned, the ON EMPTY clause provides the value of the column.
  • If ERROR ON EMPTY is specified, an error is returned.
  • If an empty sequence is returned and the ON EMPTY clause is not specified, the null value is assigned to the column.
  • If a single element sequence is returned and the type of the element is not a JSON array or a JSON object, the value is converted to the data type that was specified for the column.
  • If a single element sequence is returned and the type of the element is a JSON array or a JSON object, an error is returned.
  • If a sequence with more than one element is returned, an error is returned.
  • If an error occurs, the ON ERROR clause specifies the value of the column.

The value of column-path-expression-constant must not be an empty string or a string of all blanks. If the PATH clause is not specified, the column-path-expression-constant is defined as '$.' prefixed to column-name.

ON EMPTY
Specifies the behavior when an empty sequence is returned for the column.
NULL ON EMPTY
An SQL null value is returned. This clause is the default.
ERROR ON EMPTY
An error is returned.
DEFAULT default-expression ON EMPTY
The value that is specified by default-expression is returned. The data type of default-expression must be the same as the return data type (SQLSTATE 42815).
ON ERROR
Specifies the behavior when an error is returned for the column. If this clause is not specified, the behavior specified for the table-level ON ERROR clause is followed.
NULL ON ERROR
A null value is returned.
ERROR ON ERROR
An error is returned.
DEFAULT default-expression ON ERROR
The value that is specified by default-expression is returned. The data type of default-expression must be the same as the return data type (SQLSTATE 42815).
json-table-formatted-column-definition
Specifies an output column of the result table. The definition includes the column name, data type, and an SQL/JSON path expression. This definition is used to extract the value from the sequence item for the row. The extracted value is formatted as a JSON string.
column-name
Specifies the name of the column in the result table. The name cannot be qualified and the same name cannot be used for more than one column of the result table (SQLSTATE 42711).
data-type
Specifies the data type of the column. The data type can be CHAR, VARCHAR, CLOB, VARBINARY, or BLOB (SQLSTATE 42815).

See CREATE TABLE statement for the description of built-in data types.

FORMAT JSON
Indicates that the data that is retrieved is formatted as a JSON string.
PATH column-path-expression-constant
Specifies a character string constant that is interpreted as an SQL/JSON path.

The column-path-expression-constant specifies an SQL/JSON path expression that determines the column value regarding the result of evaluating the SQL/JSON path expression in sql-json-path-expression.

For more information about the content of an SQL/JSON path expression, see sql-json-path-expression.

Given an item from the result of processing the sql-json-path-expression as the externally provided context item, the column-path-expression-constant is evaluated and returns an output sequence. The column value is determined based on this output sequence as follows:
  • If an empty sequence is returned, the ON EMPTY clause provides the value of the column.
  • If ERROR ON EMPTY is specified, an error is returned.
  • If an empty sequence is returned and the ON EMPTY clause is not specified, the null value is assigned to the column.
  • If an error occurs, the ON ERROR clause specifies the value of the column.

The value for column-path-expression-constant must not be an empty string or a string of all blanks. If the PATH clause is not specified, the column-path-expression-constant is defined as '$.' prefixed to column-name.

WITHOUT ARRAY WRAPPER or WITH ARRAY WRAPPER
Specifies whether the output value is wrapped in a JSON array.
WITHOUT ARRAY WRAPPER
Indicates that the result is not wrapped. This clause is the default. Using a strict SQL/JSON path definition that resolves to a sequence of two or more SQL/JSON elements results in an error (SQLSTATE 2203A). Using a lax SQL/JSON path definition with the ON EMPTY that resolves to a sequence of two or more SQL/JSON elements results in an error (SQLSTATE 22035).
WITH UNCONDITIONAL ARRAY WRAPPER
Indicates that the result is enclosed in square brackets to create a JSON array.
WITH CONDITIONAL ARRAY WRAPPER
Indicates that the result is enclosed in square brackets to create a JSON array for either of the following scenarios:
  • More than one SQL/JSON element is returned.
  • A single SQL/JSON element that is not a JSON array or a JSON object is returned.
KEEP QUOTES or OMIT QUOTES
Specifies whether the surrounding quotation marks are removed when a scalar string is returned.
KEEP QUOTES
Quotation marks are not removed from scalar strings. This clause is the default.
OMIT QUOTES
Quotation marks are removed from scalar strings. When OMIT QUOTES is specified, the WITH ARRAY WRAPPER clause cannot be specified (SQLSTATE 42601).
ON EMPTY
Specifies the behavior when an empty sequence is returned for a column.
NULL ON EMPTY
An SQL null value is returned. This clause is the default.
ERROR ON EMPTY
An error is returned.
EMPTY ARRAY ON EMPTY
An empty JSON array is returned.
EMPTY OBJECT ON EMPTY
An empty JSON object is returned.
ON ERROR
Specifies the behavior when an error is returned for the column. If this clause is not specified, the behavior specified for the table-level ON ERROR clause is followed.
NULL ON ERROR
An SQL null value is returned. This clause is the default.
ERROR ON ERROR
An error is returned.
EMPTY ARRAY ON ERROR
An empty JSON array is returned.
EMPTY OBJECT ON ERROR
An empty JSON object is returned.
ERROR ON ERROR
An error is returned when a table level error is encountered.

Notes

  • If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)

Example

  1. This example is based on the following JSON document:
    {
  "id" : 901,
  "firstname" : "John",
  "lastname"  : "Doe",
  "phoneno"   : "555-3762"
}
    List the employee ID, given name, surname, and phone number: 
    SELECT U."id", U."first name", U."last name", U."phone number"
  FROM EMPLOYEE_TABLE E
    JSON_TABLE(E.jsondoc, 'strict $'
               COLUMNS( "id" INTEGER,
                        "firstname"  VARCHAR(20),
                        "lastname"   VARCHAR(20),
                        "phoneno"    VARCHAR(20))
                        ERROR ON ERROR) AS U
    This query returns the following table:
    id firstname lastname phoneno
    901 John Doe 555-3762