JSON Functions
You are looking at an older version of the documentation. The latest version is found here.
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
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
87 | Wartian Herkku | Pirkko Koskitalo | Torikatu 38 | Oulu | 90110 | Finland |
88 | Wellington Importadora | Paula Parente | Rua do Mercado, 12 | Resende | 08737-363 | Brazil |
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
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 v3.13
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 v3.13
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, whereN
is the 1-based index of the value in theJSONARRAY
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 aCLOB
.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.