JSON Concepts
JSON concepts are:
• Lax and strict modes
• JSON sequences
• JSON member accessors
• JSON API common syntax
• JSON predicates
Lax and Strict Modes
There are two modes for running JSON queries: lax and strict.
The mode determines what values or errors are returned during error or out of range conditions. For example, for out of range conditions, lax mode returns an empty sequence while strict mode returns an error. Strict mode should be used when an object is expected or required to have a specific structure.
The specific effects of lax and strict vary depending on the query and the data being accessed.
JSON Sequences
A sequence is a set of values retrieved from an JSON object using a JSON path containing a wildcard.
A sequence can be returned using the JSON_QUERY function when wrapped inside an array wrapper.
A sequence can also be passed as an operand to a comparison function or an item method.
When passed as an operand into a comparison function, each value in the sequence is tested using the comparison operator. If an invalid comparison is detected (for example, a data type conflict) then an error is returned. If a comparison returns TRUE, and the mode is lax, then TRUE is immediately returned. If the mode is strict, all values of the sequence are checked to confirm that no comparison errors occur.
When a sequence is passed to an item method, the item method is run against all values of the sequence.
JSON Member Accessors
A member accessor specifies the JSON object keys whose values are to be returned.
A $ represents the entire object and is optionally followed by a list of key names separated with a period. Key names can be delimited with double quotes. The double quotes are required if the key name has a special character.
A list of key names uses a parent-child relationship.
$.key1.key2.key3 means the base object ($) contains a key named “key1”.
The value of “key1” should be an object that contains the key “key2”.
The value of “key2” should be an object that contains the key “key3”.
If any of the above is not true, then $.key1.key2.key3 will return an error in strict mode or an empty sequence in lax mode.
A * can be used to return all objects from an object or array.
Given the following JSON object:
{ "type": "Feature",
"geom": { "type": "Polygon",
"coord": [[0, 0], [0,10], [10, 10], [10, 0], [0,0]]
}
}
The following table gives examples of JSON member accessors followed by the result value.
In lax mode the following behaviors apply:
• If an operation requires an array but the operand is not an array, then the operand is converted to a one-item array before performing the operation.
• If an array is referenced without specifying the array element, then [*] is assumed.
If $.grid is an array of objects containing the key “point”, ‘lax $.grid.point’ is equivalent to ‘strict $.grid[*].point’.
• If an operator requires something other than an array, but the operand is an array, then the array of X elements is converted to a sequence containing the same X elements. The operator is then performed on the newly created sequence.
• Arrays of size 1 can be treated as scalars. For example, ["This"] is the same as "This" and vice versa. $[0] returns a value for a scalar in lax mode.
• Any reference to a nonexistent key causes an empty sequence to be returned.
Doing any of the above in strict mode returns an error.
JSON API Common Syntax
All JSON query functions need an input JSON value to process, a path specification that indicates what values of the JSON value to use (since the input value can be an object), and other optional parameters. Queries use the JSON API common syntax to specify these items.
The JSON API common syntax has the following format:
'json_context , json_path' [json_passing]
where:
json_context
Specifies the context item—the item to run the JSON path against. Typically, it is the database column containing the JSON objects.
json_path
Specifies a JSON path expression like the following example:
'LAX $.owner ? (@.assettag == $serial )' PASSING table1.sno AS "serial"'
The path expression consists of three sections:
1. The keyword LAX or STRICT to indicate the mode
2. A member accessor (starting with $) that represents the values to be returned from the JSON context item
3. A filter expression that begins with ? followed by a predicate expression in parentheses
The filter expression causes the JSON path to return only those values that are qualified by the filter expression. It is similar to an SQL WHERE clause. In the filter expression, the special variable @ references the current JSON item in the sequence being examined.
In the example above, @.assettag refers to $.owner.assettag.
The path expression can also include a PASSING clause, described below.
json_passing
(Optional) The json_passing option is a method of using a constant or database column in the JSON path. The syntax is:
PASSING value AS identifier, [value AS identifier, ...]
Example:
PASSING t1.id AS "idno", t1.seat AS "seat"
In the JSON path, $idno is replaced with t1.id and $seat is replaced with t1.seat during query execution.
If a string column or constant uses FORMAT JSON AS in its passing clause, then the string is treated as a JSON object rather than a string. The JSON object (if a valid one) can have its key:values processed using standard member accessors. For example:
Assume a table employee with the following rows:
id datacol - a varchar(100)
100 NULL
200 {"emptype":"contractor", "office":"home"}
300 {"emptype":"intern", "salary":"hourly"}
The query:
SELECT id FROM employees e
WHERE JSON_EXISTS (e.jsoncol, 'LAX $ ? ($J2.emptype == "intern")'
PASSING e.jsoncol FORMAT JSON AS 'J2' )
returns one value: 300.
The JSON API common syntax is used in query functions to specify what values to return from JSON data stored in the database. For example:
SELECT c FROM t01 WHERE JSON_EXISTS (c, 'LAX $ ? (@.col.Name SIMILAR TO "d%"')
For more information on JSON query functions, see
JSON Query Functions.
JSON Predicates
JSON predicates work against a sequence as follows:
• In lax mode, arrays are unwrapped.
• The predicate is evaluated for all values of the input sequence.
• The return sequence is the set of values for which the predicate is true.
• If the input sequence contains three items, but only two pass the filter qualifications, then the output sequence will contain those two values.
A predicate can return true, false, or a JSON unknown (a predicate error).
An example of a predicate returning unknown: (@.hours > 9) if @.hours is a string scalar.
The following JSON predicates are available:
• EXISTS
• Comparison operators
• SIMILAR TO
• STARTS WITH
• IS UNKNOWN
EXISTS
The EXISTS predicate tests for the existence of a key. Specifically, it tests whether a member accessor returns at least one JSON value. It can be used to probe for a member before accessing it. EXISTS is especially useful for preventing errors in STRICT mode.
Example:
STRICT $ ? (EXISTS (@.name)).name
This JSON path returns an empty sequence if the base object does not contain the key “name”.
Comparison Operators
The following comparison operators are supported in the Actian X JSON implementation:
SIMILAR TO
The SIMILAR TO predicate tests if a string matches a pattern. The syntax is identical to SQL’s SIMILAR TO except in the JSON standard, the standard escape character is the backslash (\); therefore, unlike SQL’s SIMILAR TO, the escape “\\” clause is assumed and cannot be specified or changed.
Example:
SELECT c FROM t01 WHERE JSON_EXISTS (c, 'LAX $ ? (@.col.Name SIMILAR TO "d%")
STARTS WITH
The STARTS WITH predicate tests for an initial substring.
A STARTS WITH B returns true if string A starts with string B (that is, if string B is a prefix of string A).
No wildcards are allowed in either operand. To use wildcards, the SIMILAR TO predicate must be used.
If the second operand of STARTS WITH is a sequence, then TRUE is returned if the first operand starts with any value within the second operand’s sequence.
IS UNKNOWN
The IS UNKNOWN predicate tests for JSON unknown results. It lets you find invalid rows by testing a result to see if it returned a JSON unknown (which is typically an error).
Example:
SELECT id
FROM employees
WHERE JSON_EXISTS (datacol,
'LAX $ ? ( (@.age > 18) IS UNKNOWN')
This query returns all rows where (@.age > 18) returns an JSON unknown rather than true or false. In this case, most likely @.age is a non-numeric value.