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.
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. This is equivalent to the output
of the JSON_VALUE scalar function.
- 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-type1
- 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 value. This is equivalent to
the output of the JSON_QUERY scalar function.
- 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-type2
- 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
- This example is based on the following JSON
document:
List the employee ID, given name, surname, and phone number:{ "id" : 901, "firstname" : "John", "lastname" : "Doe", "phoneno" : "555-3762" }
This query returns the following table:SELECT U."id", U."firstname", U."lastname", U."phoneno" 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
id firstname lastname phoneno 901 John Doe 555-3762