SQL/JSON scalar

An SQL/JSON scalar is a non-null value of any of the following SQL types:

• Character string, using Unicode characters
• Numeric
• Boolean
• Datetime

SQL/JSON null

The SQL/JSON null differs from the SQL null in that it has a value distinct from any value of any SQL type, including the null type.

SQL/JSON array

An SQL/JSON array is an ordered list or zero or more SQL/JSON items. These items are called SQL/JSON elements of the SQL/JSON array. Array elements are separated by commas and enclosed in square brackets. For example:

[ 49.95, 67.60, "Swedish cabbage", false, "assertion"]

SQL/JSON object

A SQL/JSON object is an unordered collection of zero or more SQL/JSON members, where a member is a pair whose first value is a character string from the Unicode character set and whose second value is an SQL/JSON item. The first value of an SQL/JSON member is called the key, and the second value is called the bound value. Members are often called key/value pairs. They may also be called name/value pairs. SQL/JSON objects can be serialized by separating the members with commas and enclosing the entire object in curly braces. For example:

{ "name" : "Santa", "vehicle" : "sleigh", "home" : "North Pole" }

JSON value expression

As noted in the previous Backus-Naur Form (BNF) syntax definition, a JSON context item is just a JSON value expression. A JSON value expression is defined as

<JSON value expression> ::=

<value expression> [ <JSON input clause> ]

<JSON input clause> ::= FORMAT <JSON representation>

<JSON representation> ::=

JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ]

| Implementation-defined JSON representation option>

You see from this that a JSON value expression is a value expression with an optional input clause. The input clause specifies the format of the JSON representation, which specifies the encoding as either UTF8, UTF16, UTF32, or an implementation-defined representation.

Path expression

Following the JSON context item and a comma is the JSON path specification, which must be a character string literal. The table path name and passing clause are optional parts of the JSON path specification.

Passing clause

The passing clause, as the name implies, passes parameters to the SQL/JSON path expression.

JSON output clause

When JSON data returns to an application because of the operation of a function, the application author can specify the data type, format, and encoding of the JSON text created by the function. The syntax for the output clause is

<JSON output clause> ::=

RETURNING ,data type>

[ FORMAT <JSON representation> ]

<JSON representation> ::=

JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ]

| Implementation-defined JSON representation option>

If FORMAT is not specified, then JSON format is assumed.

The SQL/JSON query functions are JSON_EXISTS, JSON_VALUE, JSON_QUERY, and JSON_TABLE. These functions evaluate path language expressions against JSON values. The results returned are values of SQL/JSON types, which are then converted to SQL types. Path language is described later in this chapter.

JSON_EXISTS

JSON_EXISTS determines whether a JSON value satisfies a search condition in the path specification. The syntax is

<JSON exists predicate> ::=

JSON_EXISTS <left paren>

<JSON API common syntax>

[ <JSON exists error behavior> ON ERROR ]

<right paren>

<JSON exists error behavior> ::=

TRUE | FALSE | UNKNOWN | ERROR

If the optional ON ERROR clause is not included, the default assumption is FALSE ON ERROR. JSON_EXISTS evaluates the SQL/JSON path expression, returning a TRUE result if the path expression finds one or more SQL/JSON items.

Sample data that can be used to learn how to use the query functions, including JSON_EXISTS, can be found on pages 24 and 25 of Section 6 of the SQL Technical Report ISO/IEC TR 19075-6:2017(E), which can be downloaded from

https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

The data consists of two columns, K and J of a table T. K is the primary key of the table, and J is the data. The table consists of key-value pairs or arrays of key-value pairs. JASON_EXISTS tests for the existence of a given character string literal in the JSON path expression. For example

SELECT T.K

FROM T

WHERE JSON_EXISTS (T.J, 'lax $.where') ;

The primary keys of the rows that contain the word 'where' are returned as the result set of the SELECT query. The keyword 'lax' refers to the error handling that is more forgiving than 'strict' error handling. It has no effect on the result of the query. $ is the accessor that accesses the word 'where' in the current JSON object.

JSON_VALUE

The JSON_VALUE function extracts a SQL scalar value from a JSON value. The syntax is

<JSON value function> ::=

JSON VALUE <left paren>

<JSON API common syntax>

[ <JSON returning clause> ]

[ <JSON value empty behavior> ON EMPTY ]

[ <JSON value error behavior ON ERROR ]

<right paren>

<JSON returning clause> ::= RETURNING <data type>

<JSON value empty behavior> ::=

ERROR

| NULL

| DEFAULT <value expression>

<JSON value error behavior> ::=

ERROR

| NULL

| DEFAULT <value expression>

As you can probably surmise, <JSON value empty behavior> tells what to return if the result of the SQL/JSON path expression is empty.

• NULL ON EMPTY means the result of JSON_VALUE is empty.
• ERROR ON EMPTY means an exception is raised.
• DEFAULT <value expression> ON EMPTY means that the value expression is evaluated and cast to the target type.

<JSON value error behavior> is similar. It specifies what to do if there is an unhandled error.

In the preceding JSON_EXISTS example, all the rows where the keyword 'where' was present in the J value column were returned. With JSON_VALUE, the value associated with a target keyword is returned. Using the same data set as the JSON_EXISTS example, where the keyword 'who' is paired with a person’s name, the following SQL code will return the names of people from all the rows where the keyword 'who' is present.

SELECT T.K,

JSON_VALUE (T.J, 'lax $.who') AS Who

FROM T ;

The result set will contain a column named K, containing the primary keys of the rows being returned, and a column named Who, containing the names that were paired with the 'who' keyword in the source data.

By default, JSON_VALUE returns an implementation-defined character string data type. The user can specify other types with a RETURNING clause.

JSON_QUERY

Although JSON_VALUE does a fine job of extracting a scalar from an SQL/JSON value, it is unable to extract an SQL/JSON array or an SQL/JSON object from an SQL/JSON value. JSON_QUERY performs those functions. The syntax for JSON_QUERY is

<JSON query> ::=

JSON_QUERY <left paren>

<JSON API common syntax>

[ <JSON output clause> ]

[ <JSON query wrapper behavior> ]

[ <JSON query quotes behavior> QUOTES

[ ON SCALAR STRING ] ]

[ <JSON query empty behavior> ON EMPTY ]

[ <JSON query error behavior> ON ERROR ]

<right paren>

The ON EMPTY and ON ERROR clauses are similar to the ones in JSON_VALUE, and are handled the same way. The difference is that the user can specify behavior when either the empty case or the error case arises.

• If <JSON output clause> is not specified, RETURNING JSON FORMAT is the default.
• If <JSON query empty behavior> is not specified, then NULL ON EMPTY is the default.
• If <JSON query error behavior> is not specified, then NULL ON ERROR is the default.
• If <JSON query wrapper behavior> is not specified, then WITHOUT ARRAY is the default.
• If <JSON query wrapper behavior> specifies WITH and if neither CONDITIONAL nor UNCONDITIONAL is specified, then UNCONDITIONAL is the default.
• If the value of the <JSON context item> simply contained in the <JSON API common syntax> is the null value, then the result of <JSON query> is the null value.

Using the same sample data used for the JSON_EXISTS sample query and the JSON_VALUE sample query, you can add array data to the result set, along with the results obtained with the JSON_VALUE clauses.

SELECT T.K,

JSON_VALUE (T.J, 'lax $.who') AS Who,

JSON_VALUE (T.J, 'lax $.where' NULL ON EMPTY)

AS Nali,

JSON_QUERY (T.J, 'lax $.friends') AS Friends

FROM T

WHERE JSON_EXISTS (T.J, 'lax $.friends')

The WHERE JSON_EXISTS clause eliminates any rows that do not have a key-value pair for friends. If WITH ARRAY WRAPPER is specified, then array elements returned are enclosed in a pair of square brackets.

JSON TABLE

The JSON_TABLE function is complicated, much more so than the other functions previously covered. It takes JSON data as an input and generates a relational output table from that data. The syntax definition for the simplest variant of the JSON_TABLE function takes up a full page. Nested paths and plan clauses add even more complexity to what is already pretty hard to comprehend. There is not enough room here to cover JSON_TABLE to the depth that it deserves. Instead, I refer you to page 35 and following of the SQL Technical Report ISO/IEC TR 19075-6 2017(E), which you can download from

https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

JSON_OBJECT

The JSON_OBJECT function constructs JSON objects from explicit name/value pairs. The syntax is

<JSON object constructor> ::=

JSON_OBJECT <left paren>

[ <JSON name and value> [ { <comma>

<JSON name and value> } … ]

[ <JSON constructor null clause> ]

[ <JSON key uniqueness constraint> ] ]

[ <JSON output clause> ]

<right paren>

<JSON name and value> ::=

[KEY] <JSON name> VALUE <JSON value expression>

| <JSON name> <colon> <JSON value expression>

<JSON name> ::= <character value expression>

<JSON constructor null clause> ::=

NULL ON NULL

| ABSENT ON NULL

<JSON key uniqueness constraint> ::=

WITH UNIQUE [ KEYS ]

| WITHOUT UNIQUE [ KEYS ]

Some rules go along with this syntax:

• <JSON name> may not be NULL.
• <JSON value expression> may not be NULL.
• The <JSON constructor null clause>, if NULL ON NULL, produces a SQL/JSON null. If ABSENT ON NULL, it omits the key-value pair from the resulting SQL/JSON object.
• If no JSON constructor null clause is present, the default is NULL ON NULL.

JSON_OBJECTAGG

An application developer may want to construct a JSON object from an aggregation of the data in a relational table. If, for example, a table contains two columns, one with JSON names and the other with JSON values, the JSON_OBJECTAGG function can act on that data to create a JSON object. The syntax to perform this operation is

<JSON object aggregate constructor> ::=

JSON_OBJECTAGG <left paren>

<JSON name and value>

[ <JSON constructor null clause> ]

[ <JSON key uniqueness constraint> ]

[ <JSON output clause> ]

<right paren>

If <json constructor null clause> is absent, NULL ON NULL is the default.

JSON_ARRAY

To create a JSON array, based on a list of data items in a relational database table, use the JSON_ARRAY function. The syntax is

<JSON array constructor> ::=

<JSON array constructor by enumeration>

| <JSON array constructor by query>

<JSON array constructor by enumeration ::=

JSON_ARRAY <left paren>

[ <JSON value expression> [ { <comma>

<JSON value expression> }… ]

<JSON constructor null clause> ] ]

<JSON output clause>

<right paren>

<JSON array constructor by query> ::=

JSON_ARRAY <left paren>

<query expression>

[ <JSON input clause> ]

[ <JSON constructor null clause> ]

[ <JSON output clause> ]

<right paren>

JSON_ARRAY has two variants, one that produces its result from an input list of SQL values, and the other that produces its results from a query expression invoked from within the function. If the optional JSON constructor null clause is absent, the default is ABSENT ON NULL, which is the opposite of the default behavior for JSON_OBJECT.

JSON_ARRAYAGG

You see in the section on JSON_OBJECTAGG how you could construct a JSON object based on an aggregation of relational data. In the same way, you can construct a JSON array based on an aggregation of relational data. To do so, you use the JSON_ARRAYAGG constructor function. The syntax is

<JSON array aggregate constructor> ::=

JSON_ARRAYAGG <left paren>

<JSON value expression>

[ <JSON array aggregate order by clause> ]

[ <JSON constructor null clause> ]

[ <JSON output clause> ]

<right paren>

<JSON array aggregate order by clause> ::=

ORDER BY <sort specification list>

If there is no JSON constructor null clause, the default is ABSENT ON NULL. The JSON array ORDER BY clause enables the developer to order output array elements according to one or more sort specifications, similar to the way an ORDER BY clause operates in an ordinary SQL statement.