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.
- 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.
- 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.
- 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.
- 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.
| 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 or Number | BOOLEAN | The resulting value is converted to a Boolean value following the rules for Boolean assignments described in Boolean assignments. |
| String | DATE | The JSON string is converted to a date value. The JSON string must be in one
of the following formats:
|
| String | TIME | The JSON string is converted to a time value. The JSON string must be in one
of the following formats:
|
| String | TIMESTAMP | The JSON string is converted to a timestamp value. The JSON string must be in
one of the following formats:
|
| Boolean | BOOLEAN | A JSON boolean value is returned as a Boolean 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. |
| Boolean |
SMALLINT INTEGER BIGINT DECFLOAT |
A JSON boolean value is returned as a numeric value of either 0 (false) or 1 (true). |
| 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 |
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:
Returns: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 Uid 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:
Returns: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 Uid first name last name phone type phone number --- ---------- --------- ---------- ------------ 901 John Doe home 555-3762 901 John Doe work 555-8792