JSON Functions¶

The JSON functions create and transform values of type json.

The JSON functions are:

AVRO_TO_JSON
COMPLEX_TYPE_TO_JSON
JSONPATH
JSON_TO_AVRO
JSON_TO_COMPLEX_TYPE

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

COMPLEX_TYPE_TO_JSON¶

Description

The COMPLEX_TYPE_TO_JSON function converts a register or an array to a text value with a JSON document.

This function has two signatures:

Syntax 1

COMPLEX_TYPE_TO_JSON( <array value:array>, <JSON document:text>):text

array value. Required. array whose values will be used to generate the JSON.
JSON text. Required. The name of the generated JSON text.

Example 1

SELECT complex_type_to_json(
           { ROW (
               'George',
               'Washington',
               '1732-02-22',
               ROW ('3200 Mount Vernon Memorial Highway', 'Mount Vernon', 'Virginia', 'United States')
           ) },
           'values'
       )
FROM dual();

complex_type_to_json_results
{“values”:[{“value”:”George”,”value1”:”Washington”,”value2”:”1732-02-22”, “value3”:{“value”:”3200 Mount Vernon Memorial Highway”, “value1”:”Mount Vernon”,”value2”:”Virginia”,”value3”:”United States”}}]}

Syntax 2

COMPLEX_TYPE_TO_JSON( <complex type:register>):text

complex type. Required. register whose values will be used to generate the JSON.

Example 1

Imagine you have a view called usa_president with the following schema:

select complex_type_to_json(person) from usa_president

complex_type_to_json_results
{“value”:”George”,”value1”:”Washington”,”value2”:”1732-02-22”, “value3”:{“value”:”3200 Mount Vernon Memorial Highway”, “value1”:”Mount Vernon”,”value2”:”Virginia”,”value3”:”United States”}}

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

JSON_TO_COMPLEX_TYPE¶

Description

The JSON_TO_COMPLEX_TYPE function converts a JSON expression to a register complex type. This function needs two strings as parameters: the JSON text and its JSON schema representation.

Syntax

JSON_TO_COMPLEX_TYPE( <JSON document:text>, <JSON schema:text> ):text

JSON document. Required. Literal that contains a JSON expression to convert to a register complex type.
JSON schema. Required. Literal that contains a JSON schema which specifies the structure of the JSON document.

The JSON schema cannot use neither patternProperties nor itemPrefix keys. Use properties and items keys instead. The $schema valid versions are : 2019-19, V7, V6 e V4

Example 1

SELECT json_to_complex_type('{
  "firstName": "John",
  "lastName": "Doe",
  "age": 21
}
',
 '{
   "$id": "https://example.com/person.schema.json",
   "$schema": "https://json-schema.org/draft/2019-09/schema",
   "title": "Person",
   "type": "object",
   "properties": {
     "firstName": {
       "type": "string"
     },
     "lastName": {
       "type": "string"
     },
     "age": {
       "type": "integer"
     }
   }
 }') as result from dual();

json_to_complex_type_results
{“firstName”:”John”,”lastName”:”Doe”,”age”:21}

USER MANUALS

JSON Functions¶

AVRO_TO_JSON¶

COMPLEX_TYPE_TO_JSON¶

JSONPATH¶

JSON_TO_AVRO¶

JSON_TO_COMPLEX_TYPE¶