JSON_TABLE
JSON_TABLE is a SQL extension that creates a relational view of JSON data.
For examples of how to use JSON_TABLE with views, see the Views topic of the TDV User Guide. For a progressive set of JSON_TABLE examples, refer to these sections:
Syntax
JSON_TABLE has a wide variety of arguments and syntax. After remarks, definitions, and illustrations of JSON path, the examples demonstrate how JSON_TABLE can be applied to representative use cases.
Remarks
JSON_TABLE elements can be formatted with tabs, newlines, and extra space characters to make it more readable.
With JSON_TABLE you can:
• Define and create JSON data without regard to a schema or a particular pattern of use.
• Decompose the result of JSON expression evaluation into the relational rows and columns of a new, virtual table (an “in-line relational view”).
Definitions
These definitions are most easily understood with the help of examples. Examples in this document, and more in the Views topic of the TDV User Guide, illustrate how JSON_TABLE can be structured, presented, and used.
• JSON—JavaScript Object Notation. No comments are allowed in this notation.
• JSON_TABLE—The keyword JSON_TABLE followed by three ordered elements, enclosed in parentheses. The first two are cross-joined either implicitly (separated by a comma) or explicitly (separated by the keywords CROSS JOIN):
a. The JSON content provider, which can be:
A literal—A construct, enclosed in single-quotes ( ' ' ), that defines an in-line virtual table.
A column reference in an identified web data source (for example, T1, C1).
b. A path expression (see next main bullet below), enclosed in single-quotes ( ' ' ), that designates the row provider.
c. A COLUMNS clause—The word COLUMNS followed by, in parentheses, one or more comma-separated column definitions. Each column definition contains a column alias, its SQL data type, the keyword PATH, and either (1) a path expression designating the context item and object that is to occupy that column (
Example 1: A Literal JSON Table), or (2) a keyword designating a syntax element whose values are to be retrieved (
Example 3: Retrieving Object Properties and Their Values).
— An optional alias (for example, JT) for the table.
— If the source table is external (rather than an in-line virtual table), a comma followed by the name of the table (and an optional alias for that name).
— If the JSON content is provided through a column reference, the table that owns the column should be cross-joined with the JSON_TABLE.
The tables can be cross-joined either explicitly (“T1 CROSS JOIN T2”) or implicitly (“T1, T2”).
• Path expression—An expression that identifies the JSON object or objects on which to operate.
d. Context item (JSON root)—A dollar sign ($).
e. An optional path step (an object step or an array step).
Note: For column paths, a depth of only one path step is allowed (in a pattern similar to '$.title’)
— Object step—A dot (period), followed by the name of an object property. If the name includes internal dots, it must be enclosed in double quotes.
— Array step—A dot (period), followed by the name of an object property, followed by square brackets ( [ ] ). If the name includes internal dots, it must be enclosed in double quotes.
The characters inside an array step are called array slicers:
A number, or multiple numbers separated by commas, indicate the positions (counting from 1) of objects.
The keyword “to” indicates a range.
Omitting the starting number begins the range at the first element of the array.
Omitting the number after TO ends the range at the last element of the array.
Example of array steps:
.[ to 3, 6, 8 to ] — elements 1, 2, 3, 6, 8, 9, 10 (in a 10-element array)
• Property name—In a path expression, a property name must start with an alphabetic character. It can contain alphanumerics characters and some special characters (which must be enclosed in double quotes).
JSON Paths
Here are some examples of path expressions and their meanings.
Path Expression | Description |
$ | The context item (root), designating a specific JSON object. |
$.dept | Root, and path step. The value of property 'dept' of the object. |
$.dept.coffee[1] | Root, path step, and leaf step. The object that is the first element of the array that is the value of property 'coffee' of the root of the JSON object. The value of property 'coffee' is an array. |
$.dept.coffee[12, 3, 8 to 10] | The twelfth, third, eighth, ninth, and tenth elements of array 'coffee' (property of the root of the JSON object). The elements are returned in array order: third, eighth, ninth, tenth, twelfth. |
$.dept[ ].coffee[ ] | Both steps can be array steps. |
$."rest.ID_output"."rest.row" | This path expression designates a row within an external table. Notice that double quotes are used to escape the dot characters within the path elements. |
Example 1: A Literal JSON Table
This example sets up an in-line table and then selects title, author, and price (in that order) from it.
Execution results follow the query.
Query
In this example, the FROM clause provides the in-line virtual table. The JSON_TABLE literal begins right after the opening parenthesis and ends (followed by a comma) right before the path expression. The path expression specifies an array object (the virtual table) and a range from the beginning to 2. The COLUMNS clause defines columns that correspond to those requested in the SELECT. An alias of JT is applied to the table following the closing parenthesis.
SELECT
myTitle, author, price
FROM
JSON_TABLE (
'{
"store": {
"book": [
{
"category" : "reference",
"author" : "Nigel Rees",
"title" : "Sayings of the Century",
"price" : 8.95
},
{ "title":"The Rumi Collection"
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[ to 2]'
COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR(100) PATH '$.author' )) JT
ORDER BY price desc
Results
The results of executing this query are:
myTitle author price
Savings of the Century Nigel Rees 8.95
The Rumi Collection [NULL] [NULL]
Example 2: Another Literal JSON Table, with Ignored Objects
This example has a newsstand object between the two store objects, but the query ignores it and its contents. For every book record, the query requests the values of three attributes.
Query
SELECT
myTitle, author, price
FROM
JSON_TABLE (
'{
"store": {
"book": [
{
"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
},
"newsstand" : {
"magazine": [
{
"brand" : "Newsweek",
"price" : 10.00
}
]
},
"store": {
"book": [
{
"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_2",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour_2",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance_2",
"price": 17.00
}
]
}
}',
'$.store[2].book'
COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR(100) PATH '$.author' )) JT
-- ORDER BY price asc
Results
The path expression points to the second object in the array, but for that object the name test (store) does not match, so no result is returned.
Example 3: Retrieving Object Properties and Their Values
This query retrieves all of the keys and values within books. In this case, the COLUMNS clause uses keywords, instead of path expressions in single quotes, after PATH.
Query
SELECT
property, propValue
FROM
JSON_TABLE (
'{
"store": {
"book": [
{
"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book'
COLUMNS (property VARCHAR(100) PATH key,
propValue VARCHAR(200) PATH value)) JT
ORDER BY property
Results
The results list keys and their values as row entries, instead of listing values under column headings representing keys. In other words, you can use JSON_TABLE to retrieve structural information from tables, as well as values.
property propValue
author Nigel Rees
author Evelyn Waugh
author Steve Harris
category reference
category fiction
category history
price 8.95
price 15.00
price 17.00
title Savings of the Century
title Sword of Honor
title Renaissance
Example 4: JSON Content Provided by an External Table
This example uses JSON_TABLE to define a relational structure (columns) on an external table that came from a REST data source.
Query
SELECT
customerId, customerName
FROM
JSON_TABLE (
C."output",
'$."rest.customersResponse"."rest.customersOutput"."rest.row"'
COLUMNS (customerId INTEGER PATH '$."rest.customerid"',
customerName VARCHAR(100) PATH '$."rest.companyname"')) JT ,
/shared/customers_wrapper C
Results
The results are selected from the output JSON table from the REST data source.
customerId customerName
1 Able Computing
2 Anston Systems
3 Blackard Electronics
. . .
Example 5: Subquery
In this example, JSON_TABLE is embedded in a subquery and uses a REST data source.
Query
SELECT
1 C
FROM
/services/databases/system/DUAL
WHERE EXISTS
(
SELECT
customerId, price
FROM
/shared/examples/customers_wrapper C,
JSON_TABLE (
C."output",
'$."rest.customersOutput"."rest.row"'
COLUMNS (customerId INTEGER PATH '$."rest.customerid"',
price VARCHAR(100) PATH '$."rest.companyname"')) JT
WHERE
customerId = 30
)
Example 6: Conditional Logic with Key and Value Retrieval
This example illustrates the use of conditional logic to retrieve the value of different properties based on the structure of the source data. This adds flexibility when dealing with heterogeneous data sources.
Query
SELECT
firstName,
lastName,
CASE WHEN firstName IS NULL THEN fullName
ELSE firstName || ' ' || lastName END fullName,
price
FROM
JSON_TABLE (
'{
"store": {
"book": [
{
"category": "reference",
"author" : {"firstName": "Nigel" , "lastName" : "Rees"},
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": {"FN":"Evelyn Waugh"},
"title": "Sword of Honour",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[1 to 2]'
COLUMNS (author VARCHAR(100) PATH '$.author',
price VARCHAR(100) PATH '$.price')) JT,
JSON_TABLE (JT.author,
'$'
columns (firstName VARCHAR(20) PATH '$.firstName',
lastName VARCHAR(20) PATH '$.lastName',
fullName VARCHAR(20) PATH '$.FN' )) JT2
Results
The results combine data organized in two different ways, along with price, which is common to both.
firstName lastName fullName price
Nigel Rees Nigel Rees 8.95
[NULL] [NULL] Evelyn Waugh 15.00
Example 7: Invalid Keys and Values
Query
SELECT
firstName,
lastName,
CASE WHEN firstName IS NULL THEN author
ELSE firstName || ' ' || lastName END fullName,
price
FROM
JSON_TABLE (
'{
"store": {
"book": [
{
"category": "reference",
"author" : {"firstName": "Nigel" , "lastName" : "Rees"},
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": {"FN":"Evelyn Waugh"},
"title": "Sword of Honour",
"price": 15.00
},
{
"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[*]'
COLUMNS (author VARCHAR(100) PATH '$.author',
price VARCHAR(100) PATH '$.price')) JT,
JSON_TABLE (JT.author,
'$'
columns (firstName VARCHAR(20) PATH '$.firstName',
lastName VARCHAR(20) PATH '$.lastName')) JT2
Results
An error message is returned because the array designation ( $.store[*] ) contains the wildcard character, which is not supported.
com.compositesw.cdms.webapi.WebapiException: Problems encountered while resolving JSON_TABLE references: Exception 1 :
com.compositesw.cdms.services.parser.ParserException: Invalid JSON path. Cause: Compile json
path $.store.book[*] failed.. On line 32, column 6.
[parser-2931070] . . .
Example 8: Nested Arrays
In this example, store is an array that contains arrays called book. The path expression, $.store[1].book[2], retrieves property values from these nested arrays.
Query
SELECT
-- {option "DISABLE_PLAN_CACHE" }
myTitle, author, price
FROM
JSON_TABLE (
'{
"store": [{
"book":
[{
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 13.95
} ,
{
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 12.95
}
] ,
"book": [ {
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B1",
"price": 11.95
} ,
{
"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B2",
"price": 10.95
}
]
} ,
{
"book": [ {
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B1",
"price": 9.95
} ,
{
"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B2",
"price": 8.95
}
]
}
]}',
'$.store[1].book[2]'
COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR(100) PATH '$.author' ) ) JT
ORDER BY price asc
--OFFSET 1 FETCH 2 ROWS ONLY
Results
The results (with the final line of the query left commented-out), are fetched based on the PATH expression and then sorted by price:
myTitle author price
Sayings of the Century_S1-BA2-B2 Nigel Rees 10.95
Sayings of the Century_S1-BA1-B 1 Nigel Rees 12.95
If you uncomment OFFSET 1 FETCH 2 ROWS ONLY, the offset skips the first qualifying item (after the sorting by price), and even though two rows are to be fetched, only one is left to be returned:
myTitle author price
Sayings of the Century_S1-BA1-B2 1 Nigel Rees 12.95