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)
);
TEXT

And here are the sample data:

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

JSONTOXML(rootElementName, json)
SQL
  • rootElementName us 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

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

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

JSON

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

XML

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

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" }  
SQL

XML

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

Prior releases defaulted incorrectly to using uXXXX escaping rather than xXXXX. If you need to rely on that behaviour, please see the org.teiid.useXMLxEscape system property.

JSONARRAY

This function returns a JSON array.

Syntax

JSONARRAY(value...)
XML
  • 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'})
SQL

XML

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

Using JSONARRAY on a table

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

JSONOBJECT

This function returns a JSON object.

Syntax

JSONOBJECT(value [as name] ...)
SQL
  • 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")
SQL

XML

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

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"}
*/
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"]}
*/
SQL

JSONPARSE

This function validates and returns a JSON result.

Syntax

JSONPARSE(value, wellformed)
SQL
  • 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)
SQL

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"}]
*/
SQL

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"}]}
*/
SQL

Conversion to JSON

A straightforward specification-compliant conversion is used to convert values into their 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.