TABLE Customer (
CustomerId integer PRIMARY KEY,
CustomerName varchar(25),
ContactName varchar(25)
Address varchar(50),
City varchar(25),
PostalCode varchar(25),
Country varchar(25),
);
JSON functions
JSON functions provide functionality for working with JSON (JavaScript Object Notation) data.
Examples provided with XML functions use the following table structure:
with Data
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 |
Returns a JSON array.
JSONARRAY(value...)
value
is any object that can be converted to a JSON value.
For more information, see JSON functions.
Return value is JSON.
Null values will be included in the result as null literals.
mixed value example
jsonArray('a"b', 1, null, false, {d'2010-11-21'})
Would return
["a\"b",1,null,false,"2010-11-21"]
SELECT JSONARRAY(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
[88,"Wellington Importadora"]
[89,"White Clover Markets"]
Returns a JSON object.
JSONARRAY(value [as name] ...)
value
is any object that can be converted to a JSON value.
For more information, see JSON functions.
Return value is 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.
mixed value example
jsonObject('a"b' as val, 1, null as "null")
Would return
{"val":"a\"b","expr2":1,"null":null}
Using JSONOBJECT on a Table
SELECT JSONOBJECT(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
{"CustomerId":88, "CustomerName":"Wellington Importadora"}
{"CustomerId":89, "CustomerName":"White Clover Markets"}
Another example
SELECT JSONOBJECT(JSONOBJECT(CustomerId, CustomerName) as Customer)
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}}
{"Customer":{"CustomerId":89, "CustomerName":"White Clover Markets"}}
Another example
SELECT JSONOBJECT(JSONARRAY(CustomerId, CustomerName) as Customer)
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
{"Customer":[88, "Wellington Importadora"]}
{"Customer":[89, "White Clover Markets"]}
Validates and returns a JSON result.
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. Return value is JSON.
A null for either input will return null.
JSON parse of a simple literal value
jsonParse('{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}}', true)
creates a JSON array result as a Clob including null value. This is similar to JSONARRAY but aggregates its contents into single object
SELECT JSONARRAY_AGG(JSONOBJECT(CustomerId, CustomerName))
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
[{"CustomerId":88, "CustomerName":"Wellington Importadora"}, {"CustomerId":89, "CustomerName":"White Clover Markets"}]
You can also wrap array as
SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(CustomerId as id, CustomerName as name)) as Customer)
FROM Customer c
WHERE c.CustomerID >= 88;
==========================================================
{"Customer":[{"id":89,"name":"Wellington Importadora"},{"id":100,"name":"White Clover Markets"}]}
A straight-forward, specification-compliant conversion is used for converting values into their appropriate JSON document form.
-
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 not a number or +-infinity results are allowed, invalid JSON may be obtained.
-
String values are included in their escaped/quoted form.
-
Binary values are not implicitly convertable to JSON values and require a specific prior to inclusion in JSON.
-
All other values will be included as their string conversion in the appropriate escaped/quoted form.
Returns an XML document from JSON.
JSONTOXML(rootElementName, json)
rootElementName
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, see the TO_CHARS function in String functions.
The result is always a well-formed XML document.
The mapping to XML uses the following rules:
-
The current element name is initially the rootElementName, and becomes the object value name as the JSON structure is traversed.
-
All element names must be valid XML 1.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.
JSON:
Sample JSON to XML for jsonToXml(’person’, x)
{"firstName" : "John" , "children" : [ "Randy", "Judy" ]}
XML:
Sample JSON to XML for jsonToXml(’person’, x)
<?xml version="1.0" ?>
<person>
<firstName>John</firstName>
<children>Randy</children>
<children>Judy<children>
</person>
JSON:
[{"firstName" : "George" }, { "firstName" : "Jerry" }]
XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):
<?xml version="1.0" ?>
<person>
<person>
<firstName>George</firstName>
</person>
<person>
<firstName>Jerry</firstName>
</person>
</person>
JSON:
{"/invalid" : "abc" }
XML:
<?xml version="1.0" ?>
<root>
<_x002F_invalid>abc</_x002F_invalid>
</root>
Note
|
prior releases defaulted incorrectly to using uXXXX escaping rather than xXXXX. If you need to rely on that behavior see the org.teiid.useXMLxEscape system property. |
Processing of JsonPath expressions is provided by Jayway JsonPath. Please note that it uses 0-based indexing, rather than 1-based indexing. Be sure that you are familiar with the expected returns for various path expressions. For example, if a row JsonPath expression is expected to provide an array, make sure that it’s the array that you want, and not an array that would be returned automatically by an indefinite path expression.
If you encounter a situation where path names use reserved characters, such as '.', then you must use the bracketed JsonPath notation as that allows for any key, e.g. $['.key'].
For more information, see JSONTABLE.
Extracts a single JSON value as a string.
JSONPATHVALUE(value, path [, nullLeafOnMissing])
value
is a clob JSON document, path
is a JsonPath string, and nullLeafOnMissing
is a Boolean.
Return value is a string value of the resulting JSON.
If nullLeafOnMissing
is false (the default), then a path that evaluates to a leaf that is missing
will throw an exception. If nullLeafOnMissing
is true, then a null value will be returned.
If the value is an array produced by an indefinite path expression, then only the first value will be returned.
jsonPathValue('{"key":"value"}' '$.missing', true)
Would return
null
jsonPathValue('[{"key":"value1"}, {"key":"value2"}]' '$..key')
Would return
value1
Evaluate a JsonPath expression against a JSON document and return the JSON result.
JSONQUERY(value, path [, nullLeafOnMissing])
value
is a clob JSON document, path
is a JsonPath string, and nullLeafOnMissing
is a Boolean.
Return value is a JSON value.
If nullLeafOnMissing
is false (the default), then a path that evaluates to a leaf that is missing
will throw an exception. If nullLeafOnMissing
is true, then a null value will be returned.
jsonPathValue('[{"key":"value1"}, {"key":"value2"}]' '$..key')
Would return
["value1","value2"]