JSON Functions

The JSON functions create and transform values of type json.

The JSON functions are:

AVRO_TO_JSON

Description

The AVRO_TO_JSON function returns an JSON document from an AVRO document and a JSON schema. This function can be very useful when dealing JSON format data and data sources that use AVRO as an information exchange format.

Syntax

AVRO_TO_JSON( <AVRO document:blob>, <JSON schema:text> ):text
  • AVRO document. Required. Blob that contains an AVRO document which will be deserialized to JSON format.

  • JSON schema. Required. Literal that contains a JSON schema which specifies the structure of the JSON document.

If the parameters AVRO document or JSON schema are null, this functions will return null. Also, if there is any problem during deserialization, the function will return null.

Examples

Required views for examples

To simplify, we will create two views with the schemas and we will use the JSON_TO_AVRO function to generate an AVRO document.

CREATE VIEW schemaView1 as SELECT '{
    "type":"record",
    "name":"Person",
    "fields":
      [
        { "name":"name", "type":"string" },
        { "name":"age", "type":"int" }
      ]
  }' AS schema FROM Dual();

CREATE VIEW schemaView2 as SELECT '{
  "name": "subElements",
  "type":"record",
  "fields":
    [
      {
        "name":"people",
        "type":
          {
            "type": "array",
            "items":
              {
                "name":"Person",
                "type":"record",
                "fields":
                  [
                    {"name":"name", "type":"string"},
                    {"name":"age", "type":"int"}
                  ]
              }
          }
      }
    ]
}' AS schema FROM Dual();

Example 1

 select AVRO_TO_JSON(
 JSON_TO_AVRO('
   {
     "name":"Frank",
     "age":47
   }'
  ,schema)
,schema) from schemaView1;

avro_to_json

{“name”:”Frank”,”age”:47}

Example 2

SELECT AVRO_TO_JSON(
  JSON_TO_AVRO('
    {
      "people":
        [
          {"name": "Frank", "age": 47},
          {"name": "Maria", "age": 35}
        ]
    }'
   ,schema)
 ,schema) from schemaView2;

avro_to_json

{“people”:[{“name”:”Frank”,”age”:47},{“name”:”Maria”,”age”:35}]}

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> [ , <jsonOutput:boolean> ] ):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.

  • jsonOutput. Optional. Boolean that when its value is true, and the function returns a String, it comes with double quotes. When its value is false, the behavior is the same as when this parameter is not passed.

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}]

Example 3

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.

Example 4

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[0].category') as jsonpath_results
 from Dual();

jsonpath_results

reference

Example 5

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[0].category', true) as jsonpath_results
 from Dual();

jsonpath_results

“reference”

For all 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. In the “Example 4” and “Example 5” we see the difference of using the default behavior and the optional parameter jsonOutput

JSON_TO_AVRO

Description

The JSON_TO_AVRO function returns an AVRO document from a JSON document and a schema. This function can be very useful when dealing JSON format data and datasources that uses AVRO as an information exchange format.

Syntax

JSON_TO_AVRO( <JSON document:text>, <JSON schema:text> ):blob
  • JSON document. Required. Literal that contains a JSON document which is to be serialized to AVRO format.

  • JSON schema. Required. Literal that contains a JSON schema which specifies the structure of the JSON document.

If the JSON document or JSON schema are null, this functions will return null. Also, if there is any problem during serialization, the function will return null.

Examples

Example 1

SELECT json_to_avro('
 {
   "name":"Frank",
   "age":47
 }
 ','
 {
   "type":"record",
   "name":"Person",
   "fields":
     [
       { "name":"name", "type":"string" },
       { "name":"age", "type":"int" }
     ]
 }') as json_to_avro_results from Dual();

json_to_avro_results

[BINARY DATA] - 7 bytes

Example 2

 SELECT json_to_avro('
{
  "people":
    [
      {"name": "Frank", "age": 47},
      {"name": "Maria", "age": 35}
    ]
}','
{
  "name": "subElements",
  "type":"record",
  "fields":
    [
      {
        "name":"people",
        "type":
          {
            "type": "array",
            "items":
              {
                "name":"Person",
                "type":"record",
                "fields":
                  [
                    {"name":"name", "type":"string"},
                    {"name":"age", "type":"int"}
                  ]
              }
          }
      }
    ]
}') as json_to_avro_results from Dual();

json_to_avro_results

[BINARY DATA] - 16 bytes