JSONTABLE

The JSONTABLE function uses JsonPath to produce tabular output. It is implicitly a nested table and may be correlated to the preceding FROM clause entries.

Usage

CODE

JSONTABLE(value, path [, nullLeafOnMissing] COLUMNS <COLUMN>, ... ) AS name

CODE

COLUMN := name (FOR ORDINALITY | (datatype [PATH string]))

Parameters

value is a clob containing a valid JSON document;
If nullLeafOnMissing is FALSE (default), a path that evaluates to a leaf that is missing will throw an exception. If nullLeafOnMissing is TRUE, a null value will be returned;
The path string should be a valid JsonPath. If an array value is returned, each non-null element will be used to generate a row. Otherwise, a single non-null item will be used to create a single row;
A FOR ORDINALITY column is typed as integer and will return the 1-based item number as its value;
Each non-ordinality column specifies a type and optionally a PATH;
If PATH is not specified, the path will be generated from the column name: @['name'] - which will look for an object key value matching name. If PATH is specified, it must begin with @, which means that the path will be processed relative to the current row context item.

Syntax Rules

The column names must not contain duplicates;
Array types are not supported at the moment.

Examples

Use of passing, returns 1 row [1]:

CODE

SELECT * FROM JSONTABLE('{"a": {"id":1}}}', '$.a' COLUMNS id integer) x

As a nested table:

CODE

SELECT x.* FROM t, JSONTABLE(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) x

With more complicated paths:

CODE

SELECT x.* FROM JSONTABLE('[{"firstName": "John", "lastName": "Wayne", "children": []}, {"firstName": "John", "lastName": "Adams", "children":["Sue","Bob"]}]', '$.*' COLUMNS familyName string path '@.lastName', children integer path '@.children.length()' ) x

Differences from XMLTABLE

Processing of JSON to tabular results was previously recommended through the use of XMLTABLE with JSONTOXML. For most tasks, JSONTABLE provides a simpler syntax. However, there are some differences to consider:

JSONTABLE parses the JSON completely, and then processes it. XMLTABLE supports streaming processing to reduce the memory overhead;
JsonPath is not as powerful as XQuery - there are a lot of functions and operations available in XQuery/XPath that are not available in JsonPath;
JsonPath does not allow for parent references in the column paths - there is no ability to reference the root or any part of the parent hierarchy (.. in XPath).

JSONTABLE is available since v4.0.8

We don't have a way to export this macro.