Skip to main content
Skip table of contents

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:

TEXT
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


CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
87Wartian HerkkuPirkko KoskitaloTorikatu 38Oulu90110Finland
88Wellington ImportadoraPaula ParenteRua do Mercado, 12Resende08737-363Brazil
89White Clover MarketsKarl Jablonski305 - 14th Ave. S. Suite 3BSeattle98128USA

JSONTOXML

This function transforms a JSON document into an XML document.

Syntax

SQL
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

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

XML

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

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

XML

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

SQL
{"/invalid" : "abc" }  

XML

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

SQL
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:

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

2. This example code will return value1:

SQL
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

SQL
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"]:

SQL
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

CODE
JSONTABLE(value, path [, nullLeafOnMissing] COLUMNS <COLUMN>, ... ) AS name
CODE
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]:

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

CODE
SELECT x.* FROM t, JSONTABLE(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) x
  1. With more complicated paths:

CODE
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

XML
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

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

XML

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

Using JSONARRAY on a table

SQL
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

SQL
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

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

XML

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

Using JSONOBJECT on a table

SQL
SELECT JSONOBJECT(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"CustomerId":88, "CustomerName":"Wellington Importadora"} 
{"CustomerId":89, "CustomerName":"White Clover Markets"}
*/
SQL
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"}}
*/
SQL
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

SQL
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

SQL
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.

SQL
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:

SQL
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.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.