JSON Functions

The JSON functions create and transform values of type json.

The JSON functions are:

JSONPATH

Description

The JSONPATH function returns the nodes from an JSON document selected by an JSONPath expression (JSONPath API). JSONPath expressions are similar to XPath expressions for XML documents. This function can be very useful when dealing JSON format data from data sources, allowing you to filter this information using different expressions that facilitate access to this data.

Syntax

JSONPATH( <JSON document:text>, <JSONPath expression:text>):text
  • JSON document. Required. Literal that contains a JSON document over which you want to apply the JSONPath expression on.

  • JSONPath expression. Required. JSONPath expression.

Examples

Example 1

SELECT jsonpath('
 {
 "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":12.99
      },
      {
         "category":"fiction",
         "author":"Herman Melville",
         "title":"Moby Dick",
         "isbn":"0-553-21311-3",
         "price":8.99
      }
   ]
 }
 }
 ','$.store..author') as jsonpath_results
 from Dual();

jsonpath_results

[“Nigel Rees”,”Evelyn Waugh”,”Herman Melville”]

Example 2

SELECT jsonpath('
 {
 "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":12.99
      },
      {
         "category":"fiction",
         "author":"Herman Melville",
         "title":"Moby Dick",
         "isbn":"0-553-21311-3",
         "price":8.99
      }
   ]
 }
 }
 ','$.store.book[?(@.price < 10)]') as jsonpath_results
 from Dual();

jsonpath_results

[{“category”:”reference”,”author”:”Nigel Rees”,”title”:”Sayings of the Century”,”price”:8.95},{“category”:”fiction”,”author”:”Herman Melville”, “title”:”Moby Dick”,”isbn”:”0-553-21311-3”,”price”:8.99}]

SELECT jsonpath('
 {
 "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":12.99
      },
      {
         "category":"fiction",
         "author":"Herman Melville",
         "title":"Moby Dick",
         "isbn":"0-553-21311-3",
         "price":8.99
      }
   ]
 }
 }
 ','$..book.length()') as jsonpath_results
 from Dual();

jsonpath_results

3

For all three examples we use the same json value, which will contain the information about a store that contains books. In the “Example 1” we get the authors of the books in the store. In the “Example 2” we get the books whose price is less than 10. In the “Example 3” we get the number of books we have in the store.