JSON functions

JSON functions provide functionality for working with JSON (JavaScript Object Notation) data.

Sample data for examples

Examples provided with XML functions use the following table structure:

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

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

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

Wartian Herkku

Pirkko Koskitalo

Torikatu 38

Oulu

90110

Finland

Wellington Importadora

Paula Parente

Rua do Mercado, 12

Resende

08737-363

Brazil

White Clover Markets

Karl Jablonski

305 - 14th Ave. S. Suite 3B

Seattle

98128

USA

JSONARRAY

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

Using JSONARRAY on a Table

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

JSONOBJECT

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

JSONPARSE

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)

JSONARRAY_AGG

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

Conversion to JSON

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.

JSONTOXML

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:

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

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

XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):

Sample JSON to XML for jsonToXml(’person’, x) with a root array

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

JSON:

Sample JSON to XML for jsonToXml(’root’, x) with an invalid name

{"/invalid" : "abc" }

XML:

Sample JSON to XML for jsonToXml(’root’, x) with an invalid name

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

JsonPath Support

JsonPath support 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.

JSONPATHVALUE

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

JSONQUERY

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

JSON functions

JSON functions

results matching ""

No results matching ""