JSONTABLE(value, path [, nullLeafOnMissing] COLUMNS <COLUMN>, ... ) AS nameJSONTABLE
The JSONTABLE function uses JsonPath to produce tabular output. The JSONTABLE function is implicitly a nested table and can be used within FROM clauses.
Usage
COLUMN := name (FOR ORDINALITY | (datatype [PATH string]))See also JsonPath Support
Parameters
- value
-
A clob containing a valid JSON document.
- nullLeafOnMissing
-
If false (the default), then a path that evaluates to a leaf that is missing will throw an exception. If nullLeafOnMissing is true, then a null value will be returned.
- PATH
-
String should be a valid JsonPath. If an array value is returned, then 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.
- FOR ORDINALITY
-
Column typed as integer. Returns a 1-based item number as its value.
-
Each non-ordinality column specifies a type and optionally a PATH.
-
If PATH is not specified, then 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 the the current row context item.
-
Syntax Rules
-
Columns names must not contain duplicates.
-
Array types are not supported.
JSONTABLE examples
Use of passing, returns 1 row [1]:
select * from jsontable('{"a": {"id":1}}}', '$.a' COLUMNS id integer) xAs a nested table:
select x.* from t, jsontable(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) xWith more complicated paths:
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()' ) xDifferences with 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, the processes it. XMLTABLE support 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).