JSON_TABLE

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 many rows in the result table.

Notes:

¹ The ON ERROR clause can optionally be specified before the COLUMNS clause.

json-table-regular-column-definition

json-table-formatted-column-definition

json-table-ordinality-column-definition

json-table-nested-column-definition

data-type

ccsid-clause

normalize-clause

JSON-expression

Specifies an expression that returns a character, graphic, or binary string value. If a character or graphic value is returned, it must contain a correctly formatted JSON object. If a binary value is returned, it must contain the BSON representation of a JSON object.

The JSON-expression specifies the initial context item for the sql-json-path-expression, identified within sql-json-path-expression as $.

If JSON-expression is a JSON array, it will be implicitly wrapped using a key name of SYSIBM_ROOT_ARRAY modifying the JSON-expression to be a JSON object. Any path expressions provided to JSON_TABLE are implicitly modified to account for this additional key. End of change

If JSON-expression does not contain correctly formatted data, JSON_TABLE returns an empty table unless a different error behavior is specified with an ON ERROR clause.

FORMAT JSON or FORMAT BSON

Specifies how JSON-expression is to be interpreted.

FORMAT JSON: Indicates that JSON-expression contains JSON data. If JSON-expression is binary data, the data is interpreted as UTF-8 or UTF-16. The binary data cannot be encoded using an EBCDIC CCSID.
FORMAT BSON: Indicates that JSON-expression contains the BSON representation of JSON data. When FORMAT BSON is specified, JSON-expression must be a binary string data type.

If the FORMAT clause is not specified and JSON-expression is a character or graphic string, JSON-expression is treated as JSON. If JSON-expression is a binary string, JSON-expression is treated as BSON.

sql-json-path-expression

Specifies a character or graphic string expression that is interpreted as an SQL/JSON path expression. Keywords within the sql-json-path-expression must be specified in lower case. This expression returns an output sequence where each item in the sequence is used by the column definitions to generate one or more rows in the output table. If the output sequence is empty, the result of JSON_TABLE is an empty table. sql-json-path-expression must not be an empty string or a string of all blanks.

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

AS path-name

Specifies a name to be used to identify the sql-json-path-expression.

EMPTY ON ERROR or ERROR ON ERROR

Specifies the desired behavior of JSON_TABLE when a table level error is encountered.

EMPTY ON ERROR: An empty table is returned when a table level error is encountered. This is the default.
ERROR ON ERROR: An error is returned when a table level error is encountered.

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 all the result column lengths cannot exceed 64K bytes. For information on the byte counts of columns according to data type, see Maximum row sizes. This function can return up to approximately 200 columns, depending on the length of the column name and the length of the column path.

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.

data-type

Specifies the data type of the column. For CHAR and VARCHAR columns, the CCSID cannot be 65535.

PATH column-path-expression-constant

Specifies a character or graphic 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 with respect to an item that is the result of evaluating the SQL/JSON path expression in 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. If a JSON object contains more than one value with the same key, only one of the values for the key will be returned for the 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 issued.
If an empty sequence is returned and no ON EMPTY clause is 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 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 '$.' prepended to the 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 is the default.
ERROR ON EMPTY: An error is returned.
DEFAULT default-expression ON EMPTY: The value specified by default-expression is returned.

ON ERROR

Specifies the behavior when an error is returned for the column.

NULL ON ERROR: An SQL null value is returned. This is the default.
ERROR ON ERROR: An error is returned.
DEFAULT default-expression ON ERROR: The value specified by default-expression is returned.

If this clause is not specified:

If a table level ERROR ON ERROR clause is specified, an error is returned.
Otherwise, an SQL null value is returned.

json-table-formatted-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. 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.

data-type

Specifies the data type of the column. The data type must be a character or graphic type. For CHAR and VARCHAR columns, the CCSID cannot be 65535.

FORMAT JSON

Indicates that the retrieved data should be formatted as a JSON string.

PATH column-path-expression-constant

Specifies a character or graphic 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 with respect to an item that is the result of evaluating the SQL/JSON path expression in sql-json-path-expression as well as all the paths specified by prior NESTED PATHs. 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. If a JSON object contains more than one value with the same key, only one of the values for the key will be returned for the 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 no ON EMPTY clause is 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 '$.' prepended to the column-name.

WITHOUT ARRAY WRAPPER or WITH ARRAY WRAPPER

Specifies whether the output value should be wrapped in a JSON array.

WITHOUT ARRAY WRAPPER: Indicates that the result is not wrapped. This is the default. Using an SQL/JSON path that results in a sequence of two or more SQL/JSON elements results in an error.
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 if more than one SQL/JSON element is returned.

KEEP QUOTES or OMIT QUOTES

Specifies whether the surrounding quotes should be removed when a scalar string is returned.

KEEP QUOTES: Indicates quotes are not removed from scalar strings. This is the default.
OMIT QUOTES: Indicates quotes are removed from scalar strings. When OMIT QUOTES is specified, the WITH ARRAY WRAPPER clause cannot be specified

ON EMPTY

Specifies the behavior when an empty sequence is returned for a column.

NULL ON EMPTY: An SQL null value is returned. This 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 a column.

NULL ON ERROR: An SQL null value is returned.
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.

If this clause is not specified:

If a table level ERROR ON ERROR clause is specified, an error is returned.
Otherwise, an SQL null value is returned.

json-table-ordinality-column-definition

Specifies an ordinality column of the result table.

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.

FOR ORDINALITY

Specifies that column-name is the ordinality column of the result table for the enclosing nesting level. The data type of this column is BIGINT.

If the ordinality column is not within a nested column definition, the rows of the result table are numbered sequentially, starting with 1, and the ordinality column contains the sequential number of the current row.
If the ordinality column is within a nested column definition, the rows produced by the enclosing nested column definition are numbered sequentially, starting with 1, and the ordinality column contains the sequential number of the current row. If the enclosing nested column definition is within an outer nested column definition, the numbering restarts with 1 each time the path expression of the enclosing nested column definition is applied to the result of the outer nested column definition.

json-table-nested-column-definition

Specifies one or more columns that are nested at the current level.

NESTED PATH nested-path-expression-constant: Specifies a character or graphic string constant that is interpreted as an SQL/JSON path. The nested-path-expression-constant specifies an SQL/JSON path expression that determines the column value with respect to an item that is the result of evaluating the SQL/JSON path expression in sql-json-path-expression as well as the nested-path-expression-constant from prior NESTED PATHs. Given an item from the result of processing the sql-json-path-expression as the externally provided context item and the nested-path-expression-constant from prior NESTED PATHs, the nested-path-expression-constant is evaluated and is used as the context for the nested columns.
AS nested-path-name: Specifies the name of the current path.
COLUMNS: Specifies the columns to be included in this nesting level.

Table 1. Supported JSON to SQL result column conversions
JSON type	SQL type	Notes
Number	SMALLINT INTEGER BIGINT	If the source value is beyond the range of the target data type, an overflow error is returned.
Number	DECIMAL NUMERIC	The resulting number value is converted, if necessary, to the precision and scale of the target data type. The necessary number of leading zeros are added or removed. In the fractional part of the number, the necessary number of trailing zeros are added or the necessary number of digits are eliminated. This truncation behavior is similar to the behavior of the cast from DECIMAL to DECIMAL.
Number	FLOAT DOUBLE REAL DECFLOAT	If the source value is beyond the range of the target data type, an overflow error is returned. If the source value contains more significant digits than the precision of the target data type, the source value is rounded to the precision of the target data type.
String or Number	CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB	The resulting value is converted, if necessary, to the CCSID of the target data type using the rules described in Conversion rules for assignments before it is converted to the target type with a limited length. Truncation occurs if the specified length limit is smaller than the length of the resulting string after CCSID conversion. A warning occurs if any non-blank characters are truncated. If the target type is a fixed-length string (CHAR or GRAPHIC) and the specified length of the target type is greater than the length of the resulting string from CCSID conversion, blanks are padded at the end. This truncation and padding behavior is similar to retrieval assignment of character or graphic strings.
String	DATE	The JSON string is converted to a date value. The JSON string must be in one of the following formats: ISO yyyy-mm-dd USA mm/dd/yyyy EUR dd.mm.yyyy JIS yyyy-mm-dd
String	TIME	The JSON string is converted to a time value. The JSON string must be in one of the following formats: ISO hh.mm.ss EUR hh.mm.ss JIS hh:mm:ss HMS hh:mm:ss
String	TIMESTAMP	The JSON string is converted to a timestamp value. The JSON string must be in one of the following formats: ISO yyyy-mm-dd hh:mm:ss.nnnnnn IBMSQL yyyy-mm-dd-hh.mm.ss.nnnnnn ISO-8601 yyyy-mm-ddThh:mm:ss.nnnnnn<+/-Offset> If the ISO-8601 timestamp includes an offset, the timestamp is adjusted using that offset. For example, 2021-03-18T03:00:00.0-02:00 is converted to 2021-03-18 05:00:00.000000.
Array or Object	CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB	Must be returned using a FORMAT JSON column. JSON arrays or objects are returned as JSON formatted character strings. The resultant conversion is that same as for string types.
Null	Any	A JSON null value is converted to an SQL null value
Boolean	CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB	A JSON boolean value is converted to either a `true` or `false` string and is returned as a string using the conversion rules for JSON strings.

Examples

These examples operate on the following JSON document:

{ 
  "id" : 901,
  "name" : { "first":"John", "last":"Doe" },
  "phones": [ { "type":"home", "number":"555-3762"},
              { "type":"work", "number":"555-8792"}]
}

List the employee id, first name, last name, and first phone type and number:

SELECT U."id", U."first name",U."last name",U."phone type",U."phone number"
  FROM EMPLOYEE_TABLE E,
    JSON_TABLE(E.jsondoc, 
               'lax $'
               COLUMNS( "id" INTEGER,
                        "first name"  VARCHAR(20) PATH 'lax $.name.first',
                        "last name"   VARCHAR(20) PATH 'lax $.name.last',
                        "phone type"  VARCHAR(20) PATH 'lax $.phones[0].type',
                        "phone number"  VARCHAR(20) PATH 'lax $.phones[0].number')
                      ) AS U

Returns:

id    first name    last name    phone type    phone number
---   ----------    ---------    ----------    ------------
901   John          Doe          home          555-3762

List the employee id, first name, last name, and all available telephone types and numbers:

SELECT U."id", U."first name",U."last name",U."phone type",U."number" AS "phone number"
  FROM EMPLOYEE_TABLE E,
    JSON_TABLE(E.jsondoc, 
               'lax $'
                COLUMNS( "id" INTEGER,
                         "first name"  VARCHAR(20) PATH 'lax $.name.first',
                         "last name"   VARCHAR(20) PATH 'lax $.name.last',
                         NESTED PATH 'lax $.phones[*]'
                           COLUMNS (
                                    "phone type"  VARCHAR(20) PATH 'lax $.type',
                                    "number"  VARCHAR(20) )
                                        )
                        ) AS U

Returns:

id    first name    last name    phone type    phone number
---   ----------    ---------    ----------    ------------
901   John          Doe          home          555-3762
901   John          Doe          work          555-8792