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