JSON Functions

JSON functions provide functionality for working with JSON (JavaScript Object Notation) data. On this page, we will go over all JSON functions one by one, using the same sample table structure and data set for examples. 

Sample Data For Examples

Here is the table structure we will be using in our examples:

TABLE Customer (
    CustomerId integer PRIMARY KEY,
    CustomerName varchar(25),
    ContactName varchar(25),
    Address varchar(50),
    City varchar(25),
    PostalCode varchar(25),
    Country varchar(25)
);

And here are the sample data:

To view the full table, click the expand button in its top right corner

JSONTOXML

This function transforms a JSON document into an XML document.

Syntax

JSONTOXML(rootElementName, json)

• rootElementName is the current element name. It becomes the object value name as the JSON structure is traversed and is a string;
• json is in {clob, blob};
• Return value is XML.

The appropriate UTF encoding (8, 16LE, 16BE, 32LE, 32BE) will be detected for JSON blobs. If another encoding is used, please see the to_chars function.
The result of this function is always a well-formed XML document, and the mapping to XML uses the following rules:

• All element names must be valid XML1.1 names. Invalid names are fully escaped according to the SQLXML specification;
• Each object or primitive value will be enclosed in an element with the current name;
• Unless an array value is the root, it will not be enclosed in an additional element;
• Null values will be represented by an empty element with the attribute xsi:nil="true";
• Boolean and numerical value elements will have the attribute xsi:type set to boolean and decimal, respectively.

Examples

Sample JSON to XML for JSONTOXML('person', x)

JSON

{"firstName" : "John" , "children" : [ "Randy", "Judy" ]}

XML

<?xml version="1.0"?>
	<person> 
	<firstName>John</firstName>
	<children>Randy</children>
	<children>Judy<children>
</person>

Sample JSON to XML for JSONTOXML('person', x) with a root array

JSON

[{"firstName" : "George" }, { "firstName" : "Jerry" }]

XML

<?xml version="1.0" ?>
    <person>
        <person>
            <firstName>George</firstName>
        </person>
        <person>
        	<firstName>Jerry</firstName>
    	</person>
</person>

Please note that there is an extra "person" wrapping element in this example to keep the XML well-formed.

Sample JSON to XML for JSONTOXML('root', x) with an invalid name

JSON

{"/invalid" : "abc" }  

XML

<?xml version="1.0" ?> <root> <_x002F_invalid>abc</_x002F_invalid> </root>

Prior releases defaulted incorrectly to using uXXXX escaping rather than xXXXX. Please see the system property if you need to rely on that behaviour.

JsonPath Support

JsonPath support is provided by Jayway JsonPath. Please note that it uses 0, not 1-based indexing and check the expected returns for various path expressions. If for example a row JsonPath expression is expected to provide an array, make sure that it is the array you want and not an array or an array that would automatically be returned by an indefinite path expression.

If you encounter a situation where path names use reserved characters, such as '.', use the bracketed JsonPath notation as that allows for any key, e.g. $['.key'].

JSONPATHVALUE

This function extracts a single JSON value as a string.

Syntax

JSONPATHVALUE(value, path [, nullLeafOnMissing])

• value is a clob JSON document;
• path is a JsonPath string;
• nullLeafOnMissing is a boolean;
• Return value is a string value of the resulting JSON.

If nullLeafOnMissing is FALSE (default), a path that evaluates to a leaf that is missing will throw an exception. If nullLeafOnMissing is TRUE, a null value will be returned.

If the value is an array produced by an indefinite path expression, only the first value will be returned.

Examples

1. This example code will return null:

jsonPathValue('{"key":"value"}', '$.missing', true)

2. This example code will return value1:

SELECT jsonPathValue('[{"key":"value1"}, {"key":"value2"}]', '$..key');;

JSONPATHVALUE is available since v4.0.8 

JSONQUERY

This function evaluates a JsonPath expression against a JSON document and returns the JSON result.

Syntax

JSONQUERY(value, path [, nullLeafOnMissing])

• value is a clob JSON document;
• path is a JsonPath string;
• nullLeafOnMissing is a boolean;
• Return value is a JSON value.

If nullLeafOnMissing is FALSE (default), a path that evaluates to a leaf that is missing will throw an exception. If nullLeafOnMissing is TRUE, a null value will be returned.

Example

This example code will return ["value1","value2"]:

SELECT JSONQUERY('{"key":"value"}','$.missing', true);;

JSONQUERY is available since v4.0.8 

JSONTABLE

The JSONTABLE function uses JsonPath to produce tabular output. It is implicitly a nested table and may be correlated to the preceding FROM clause entries.

Usage

JSONTABLE(value, path [, nullLeafOnMissing] COLUMNS <COLUMN>, ... ) AS name

COLUMN := name (FOR ORDINALITY | (datatype [PATH string]))

Parameters

• value is a clob containing a valid JSON document;

• If nullLeafOnMissing is FALSE (default), a path that evaluates to a leaf that is missing will throw an exception. If nullLeafOnMissing is TRUE, a null value will be returned;

• The path string should be a valid JsonPath. If an array value is returned, each non-null element will be used to generate a row. Otherwise, a single non-null item will be used to create a single row;

• A FOR ORDINALITY column is typed as integer and will return the 1-based item number as its value;

• Each non-ordinality column specifies a type and optionally a PATH;

• If PATH is not specified, the path will be generated from the column name: @['name'] - which will look for an object key value matching name. If PATH is specified, it must begin with @, which means that the path will be processed relative to the current row context item.

Syntax Rules

• The column names must not contain duplicates;

• Array types are not supported at the moment.

Examples

1. Use of passing, returns 1 row [1]:

SELECT * FROM JSONTABLE('{"a": {"id":1}}}', '$.a' COLUMNS id integer) x

1. As a nested table:

SELECT x.* FROM t, JSONTABLE(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) x

1. With more complicated paths:

SELECT x.* FROM JSONTABLE('[{"firstName": "John", "lastName": "Wayne", "children": []}, {"firstName": "John", "lastName": "Adams", "children":["Sue","Bob"]}]', '$.*' COLUMNS familyName string path '@.lastName', children integer path '@.children.length()' ) x

JSONTABLE is available since v4.0.8

JSONARRAY

This function returns a JSON array.

Syntax

JSONARRAY(value...)

• The value can be any object convertible to a JSON value;
• The return value is a CLOB marked as being valid JSON;
• Null values will be included in the result as null literals.

Examples

Mixed value example

JSON

JSONARRAY('a"b', 1, NULL, FALSE, {d'2010-11-21'})

XML

["a\"b",1,null,false,"2010-11-21"]

Using JSONARRAY on a table

SELECT JSONARRAY(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
[88,"Wellington Importadora"] 
[89,"White Clover Markets"]
*/

JSONOBJECT

This function returns a JSON object.

Syntax

JSONOBJECT(value [as name] ...)

• The value is any object convertible to a JSON value. The return value is a clob marked as being valid JSON;
• Null values will be included in the result as null literals;
• If a name is not supplied and the expression is a column reference, the column name will be used. Otherwise, exprN will be used, where N is the 1-based index of the value in the JSONARRAY expression.

Examples

Mixed value example

JSON

JSONOBJECT('a"b' AS val, 1, NULL as "null")

XML

{"val":"a\"b","expr2":1,"null":null}

Using JSONOBJECT on a table

SELECT JSONOBJECT(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"CustomerId":88, "CustomerName":"Wellington Importadora"} 
{"CustomerId":89, "CustomerName":"White Clover Markets"}
*/

SELECT JSONOBJECT(JSONOBJECT(CustomerId, CustomerName) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}} 
{"Customer":{"CustomerId":89, "CustomerName":"White Clover Markets"}}
*/

SELECT JSONOBJECT(JSONARRAY(CustomerId, CustomerName) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":[88, "Wellington Importadora"]} {"Customer":[89, "White Clover Markets"]}
*/

JSONPARSE

This function validates and returns a JSON result.

Syntax

JSONPARSE(value, wellformed)

• Value is blob with an appropriate JSON binary encoding (UTF-8, UTF-16, or UTF-32) or a CLOB. wellformed is a boolean indicating that validation should be skipped;
• The return value is a clob marked as being valid JSON;
• A null for either input will return null.

Example

JSON parsing of a simple literal value

jsonParse('{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}}', true)

JSONARRAY_AGG

This function creates a JSON array result as a CLOB including null value. This is similar to JSONARRAY, but aggregates its contents into a single object.

SELECT JSONARRAY_AGG(JSONOBJECT(CustomerId, CustomerName))
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
[{"CustomerId":88, "CustomerName":"Wellington Importadora"}, 
{"CustomerId":89, "CustomerName":"White Clover Markets"}]
*/

There is a different way to wrap an array - as follows:

SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(CustomerId AS id, CustomerName AS name)) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":[{"id":89,"name":"Wellington Importadora"},{"id":100,"name":"White Clover Markets"}]}
*/

Conversion to JSON

A straightforward specification-compliant conversion is used to convert values into appropriate JSON document form. The rules are as follows:

• Null values are included as the null literal;

• Values parsed as JSON or returned from a JSON construction function (JSONPARSE, JSONARRAY, JSONARRAY_AGG) will be directly appended into a JSON result;

• Boolean values are included as true/false literals;

• Numeric values are included as their default string conversion. In some circumstances, if numbers or +-infinity results are allowed, invalid JSON may be obtained;

• String values are included in their escaped/quoted form;

• Binary values are not implicitly convertible to JSON values and require a specific before inclusion in JSON;

• All other values are included as their string conversion in the appropriate escaped/quoted form.