DB2 Table function - JSON_TABLE - www.cadcobol.com.br

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

json-table-regular-column-definition

json-table-formatted-column-definition

data-type1

data-type2

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.

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:

   {
    "id" : 901,
    "firstname" : "John",
    "lastname"  : "Doe",
    "phoneno"   : "555-3762"
   }

List the employee ID, given name, surname, and phone number:

   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

This query returns the following table:

id	firstname	lastname	phoneno
901	John	Doe	555-3762