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:

Example 1: A Literal JSON Table
Example 2: Another Literal JSON Table, with Ignored Objects
Example 3: Retrieving Object Properties and Their Values
Example 4: JSON Content Provided by an External Table
Example 5: Subquery
Example 6: Conditional Logic with Key and Value Retrieval
Example 7: Invalid Keys and Values
Example 8: Nested Arrays

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):

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).

A path expression (see next main bullet below), enclosed in single-quotes ( ' ' ), that designates the row provider.

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.

Context item (JSON root)—A dollar sign ($).

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:

myTitleauthorprice
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.

propertypropValue
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.

firstNamelastNamefullNameprice
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:

myTitleauthorprice
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:

myTitleauthorprice
Sayings of the Century_S1-BA1-B2							1			Nigel Rees						12.95