Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

The SQL/JSON Path Language

An SQL/JSON path expression says which items of JSON text JSON_VALUE, JSON_QUERY, JSON_EXISTS and JSON_TABLE take (Value Expressions). It is a character string literal, parsed as the statement is prepared, so a path that is none is refused then. Unlike SQL, the language is case-sensitive: $.Name is not $.name, and the key words lax, strict, true, false, null, last and the methods are lower case.

The examples on this page are run in order by the user ANN.

Grammar

path        ::= { lax | strict } wff
wff         ::= additive
additive    ::= multiplicative [ { + | - } multiplicative ]...
multiplicative ::= unary [ { * | / | % } unary ]...
unary       ::= [ + | - ] accessed
accessed    ::= primary [ accessor ]...
primary     ::= $ | @ | $name | last | literal | ( wff )
accessor    ::= .key | ."key" | .* | [ subscript, ... ] | [*]
              | .method ( [ arguments ] ) | ? ( predicate )
subscript   ::= wff [ to wff ]
literal     ::= number | "string" | true | false | null
predicate   ::= disjunct [ || disjunct ]...
disjunct    ::= conjunct [ && conjunct ]...
conjunct    ::= ! ( predicate ) | ( predicate ) is unknown | ( predicate )
              | wff { == | != | <> | < | <= | > | >= } wff
              | exists ( wff ) | wff like_regex "pattern" [ flag "flags" ]
              | wff starts with { "string" | $name }

Accessors bind tightest, then a sign, then *, / and %, then + and -; in a predicate, ! before && before ||. So -$.a is the negation of $.a, and 1 + 2 * 3 is 7. $ is the JSON text, @ the item a filter tests, $name a variable of PASSING, and last the last subscript of the array a subscript is of. A number is written as in JSON, or in hexadecimal, 0x1F; a string in double quotation marks, with JSON's escapes.

VALUES (JSON_VALUE('{"a":2}', 'lax -$.a + 1 * 3' RETURNING INTEGER), JSON_VALUE('[]', 'lax 0x1F' RETURNING INTEGER),
        JSON_VALUE('{"a b":"x"}', 'lax $."a b"'));
              |              |
--------------+--------------+--------------------------------------------------------------
            1 |           31 | x
(1 row)

Lax and strict

The mode says what happens where the text is not shaped as the path expects. In lax mode an array is taken for its elements where a member accessor or a method wants one item, a single item is taken for an array of one where an array accessor wants an array, and a member or element that is not there is no item. In strict mode each is an exception condition, which ON ERROR then replaces.

VALUES (JSON_QUERY('[{"a":1},{"a":2}]', 'lax $.a' WITH WRAPPER), JSON_VALUE('{"a":5}', 'lax $[0].a'),
        JSON_VALUE('{}', 'lax $.b'));
VALUES JSON_VALUE('[{"a":1}]', 'strict $.a' ERROR ON ERROR);
VALUES JSON_VALUE('{"a":5}', 'strict $[0]' ERROR ON ERROR);
VALUES JSON_VALUE('[1]', 'strict $[3]' ERROR ON ERROR);
                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 [1,2]                                                        | 5                                                            | NULL
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2203A): an SQL/JSON item is not an object with a member of the key a [ISO/IEC 9075-2:2023 §9.46 GR11g ii 1 B II]
turborel: SQLCODE -301 (SQLSTATE 22039): an SQL/JSON item is not an array [ISO/IEC 9075-2:2023 §9.46 GR11g ii 3 B]
turborel: SQLCODE -301 (SQLSTATE 22033): an SQL/JSON subscript is outside its array, or the first of a range past the last [ISO/IEC 9075-2:2023 §9.46 GR11g ii 3 F II 2 a]

Accessors

Accessor Gives
.key, ."key" the value of the member of that key of each object
.* the value of every member of each object, in order
[n], [n to m], [n, m] the elements of those subscripts of each array, counted from 0
[last], [last - 1] the last element, and the one before it
[*] every element of each array
? (predicate) each item the predicate is true of

A subscript is a number, made a whole number by truncation, or an expression of one; n to m is every subscript from n to m. In lax mode a subscript outside the array gives nothing; in strict mode it is SQLSTATE 22033, as is a subscript that is no number in either mode.

VALUES (JSON_QUERY('[10,20,30,40]', 'lax $[1 to last]' WITH WRAPPER), JSON_QUERY('[10,20,30,40]', 'lax $[0, last - 1]' WITH WRAPPER),
        JSON_QUERY('{"a":1,"b":[2]}', 'lax $.*' WITH WRAPPER));
                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 [20,30,40]                                                   | [10,30]                                                      | [1,[2]]
(1 row)

Filters

A filter, ? (predicate), keeps each item its predicate is true of; in the predicate @ is that item. A predicate is true, false or unknown. An exception condition within it, as an operand that raises one, makes it unknown, so the item is not kept and the condition is not raised. ==, !=, <, <=, > and >= compare items of a kind. Numbers compare by value, strings by their characters' code points, and truth values and datetimes as SQL compares them. <> is !=. JSON's null equals null and is unequal to every other item. A comparison of items of two other kinds, or of an array or object, is unknown. Of operands of several items, a comparison is true if any pair compares true; in strict mode it is unknown if any pair cannot be compared.

exists (wff) is true if the expression gives an item. starts with is true of a string that begins with the string, or the variable's string, after it. (predicate) is unknown is true if the predicate is unknown. !, && and || are not, and, or, of three values.

VALUES (JSON_QUERY('[1,"2",3,null]', 'lax $[*] ? (@ > 1)' WITH WRAPPER),
        JSON_QUERY('[{"n":"Ada"},{"n":"Alan"},{"m":1}]', 'lax $[*] ? (@.n starts with "Al").n' WITH WRAPPER),
        JSON_QUERY('[1,"x",3]', 'lax $[*] ? ((@ > 2) is unknown)' WITH WRAPPER),
        JSON_QUERY('[{"a":1},{"b":2}]', 'lax $[*] ? (exists (@.a))' WITH WRAPPER));
                                                              |                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 [3]                                                          | ["Alan"]                                                     | ["x"]                                                        | [{"a":1}]
(1 row)

like_regex is true of a string the XQuery regular expression matches anywhere in it, as LIKE_REGEX in SQL (Regular expressions). Its flags are letters, in any order:

Flag Means
i case is ignored
s . matches a newline too
m ^ and $ match at the ends of each line
x white space in the pattern, outside a class, is ignored
q the pattern is matched as its characters, none special; s, m and x do nothing beside it

Another letter is SQLSTATE 2201T, and a pattern that is no regular expression 2201S, each as the statement is prepared.

VALUES (JSON_QUERY('["Apple","apricot","Banana"]', 'lax $[*] ? (@ like_regex "^ap" flag "i")' WITH WRAPPER),
        JSON_QUERY('["a.c","abc"]', 'lax $[*] ? (@ like_regex "a.c" flag "q")' WITH WRAPPER));
VALUES JSON_VALUE('"x"', 'lax $ ? (@ like_regex "x" flag "z")');
                                                              |
--------------------------------------------------------------+--------------------------------------------------------------
 ["Apple","apricot"]                                          | ["a.c"]
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2201T): 'z' is not an XQuery option flag: 'z' is none of s, m, i, x and q [ISO/IEC 9075-2:2023 §9.28 GR5] (at character 25)

Arithmetic

+, -, *, / and % compute with numbers as SQL does, each operand one number. An operand of no item or of several is SQLSTATE 22038, and of an item that is no number 22036. A sign applies to each item its operand gives, each a number, else 2203B. Division by zero is 22012, and a result past the range of its type 22003.

% is the remainder of exact whole numbers, as MOD is [ISO/IEC 9075-2:2023 §6.31 SR12]: of an operand that is approximate or has a fraction it is SQLSTATE 22000. A whole number written with a decimal point, 4.0, is whole.

VALUES (JSON_VALUE('{"a":7,"b":2}', 'lax $.a % $.b'), JSON_VALUE('{"a":7,"b":2}', 'lax $.a / $.b'),
        JSON_QUERY('[1,2]', 'lax -$[*].double()' WITH ARRAY WRAPPER));
VALUES JSON_VALUE('[1,2]', 'lax $[*] + 1' ERROR ON ERROR);
VALUES JSON_VALUE('5e0', 'lax $ % 2' ERROR ON ERROR);
                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 1                                                            | 3.5                                                          | [-1.0E0,-2.0E0]
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22038): an operand of an arithmetic operator is not a singleton numeric value [ISO/IEC 9075-2:2023 §9.46 GR11j ii 1]
turborel: SQLCODE -301 (SQLSTATE 22000): an operand of MOD is not a whole exact or decimal floating-point number of at most 38 digits [ISO/IEC 9075-2:2023 §6.31 SR12]

Item methods

A method applies to each item it is given; in lax mode an array is first taken for its elements, but by type(), size() and keyvalue(). The casting methods cast as CAST does, so an item they take but CAST does not is the cast's exception, as "x".date() is 22018.

Method Of Gives Else
type() any item its kind: "null", "boolean", "number", "string", "array", "object", "date", "time without time zone", "time with time zone", "timestamp without time zone", "timestamp with time zone"
size() an array; any other item, in lax mode how many elements; 1 of another item 22039 in strict mode
double() a number or a string of one DOUBLE PRECISION 22036
ceiling(), floor(), abs() a number the least whole number not below it, the greatest not above, its magnitude, of its type; never negative zero 22036
bigint(), integer() a number or a string BIGINT, INTEGER 22036
decimal([p [, s]]) a number or a string DECIMAL(p, s), of the defaults if not written 22036
number() a number or a string a number of its own type; of a string, of the literal it is 22036
boolean() a truth value or a string BOOLEAN 2202V
string() a string, a number, a truth value or a datetime CHARACTER VARYING(32767) 2202X
date() a string DATE 2202W
time([p]), timestamp([p]) a string TIME(p), TIMESTAMP(p) 2202Y, 2202Z
time_tz([p]), timestamp_tz([p]) a string; a number, which CAST takes to no datetime, 22005 TIME(p) WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE 2202Y, 2202Z
datetime() a string of a date, time or timestamp literal the datetime it is 22031
datetime("template") a string the template reads the datetime the template makes 22031
keyvalue() an object an object for each member: its key, value and id 2203C

datetime() reads a date, a time or a timestamp, each with a time zone or without, as its literal is written; datetime("template") reads by the template's fields, as CAST's FORMAT does. It gives a timestamp if the template has fields of the date and of the time, a date or a time if of one, with a time zone if it has TZH. A time or timestamp of no time zone that a comparison or timestamp_tz() sets beside one with a zone is taken in the SQL-session's time zone, with a template and without alike.

VALUES (JSON_VALUE('[1,2]', 'lax $.size()'), JSON_VALUE('{"a":-1.5}', 'lax $.a.abs()'),
        JSON_VALUE('-0.5e0', 'lax $.ceiling()'), JSON_VALUE('"12.345"', 'lax $.decimal(4,2)'),
        JSON_VALUE('"2024-02-29 10:00:00"', 'lax $.datetime().type()'),
        JSON_VALUE('"29/02/2024"', 'lax $.datetime("DD/MM/YYYY")' RETURNING DATE));
VALUES JSON_VALUE('{}', 'lax $.ceiling()' ERROR ON ERROR);
VALUES JSON_VALUE('"x"', 'lax $.date()' ERROR ON ERROR);
                                                              |                                                              |                                                              |                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+------------
 2                                                            | 1.5                                                          | 0E0                                                          | 12.34                                                        | timestamp without time zone                                  | 2024-02-29
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22036): an SQL/JSON item of an item method is not a number [ISO/IEC 9075-2:2023 §9.46 GR11g ii 6 C IV 1 a]
turborel: SQLCODE -301 (SQLSTATE 22018): 'x' is no DATE literal's string [ISO/IEC 9075:1992 §6.10 GR9a ii, GR10a ii, GR11a ii]

keyvalue()

keyvalue() gives an object's members as objects of three members: key, the member's key, value, its value, and id, a number that identifies the object the member is of. Each object of the JSON text and of the PASSING variables has an id of its own, the same however the path reaches it, so the members of one object share it and those of two objects differ. Of an item that is no object, keyvalue() is SQLSTATE 2203C, and in lax mode it is applied to each element of an array.

VALUES JSON_QUERY('{"a":1,"b":{"c":2}}', 'lax $.keyvalue()' RETURNING VARCHAR(100) WITH WRAPPER);
VALUES JSON_QUERY('[{"x":1},{"y":2}]', 'lax $.keyvalue().id' WITH WRAPPER);
VALUES JSON_QUERY('{"price":4,"qty":3}', 'lax $.keyvalue() ? (@.value > 3).key' WITH WRAPPER);

--------------------------------------------------------------
 [{"key":"a","value":1,"id":0},{"key":"b","value":{"c":2},"id":0}]
(1 row)

--------------------------------------------------------------
 [1,3]
(1 row)

--------------------------------------------------------------
 ["price"]
(1 row)

Variables

$name is the value PASSING gives as name, an identifier as SQL reads one. A regular identifier is upper case, so PASSING 2 AS n is $N. A string, number, truth value or datetime is the item it is; FORMAT JSON makes a string's JSON text the item. A variable the path names and PASSING does not is SQLSTATE 2A000, SQLCODE -103, as the statement is prepared.

VALUES (JSON_QUERY('[1,5,9]', 'lax $[*] ? (@ > $LOW && @ < $HIGH)' PASSING 2 AS LOW, 9 AS HIGH WITH WRAPPER),
        JSON_VALUE('{}', 'lax $J.a' PASSING '{"a":"x"}' FORMAT JSON AS J),
        JSON_VALUE('{}', 'lax $D.type()' PASSING DATE '2024-02-29' AS D));
                                                              |                                                              |
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 [5]                                                          | x                                                            | date
(1 row)

Numbers

A number of JSON text is exact if it is written without an exponent, approximate, DOUBLE PRECISION, if with one. An exact number of more than 18 significant digits, or a fraction of more than 18 digits, is DECFLOAT, of at most 34 significant digits; leading zeros and the trailing zeros of a fraction are not significant. A number of more significant digits, or one past the range of DOUBLE PRECISION or of DECFLOAT, is SQLSTATE 22003 wherever JSON text is read, IS JSON too. A number literal of a path past those limits is refused, SQLCODE -900, as the statement is prepared.

VALUES (JSON_VALUE('0.10000000000000000000000000000000000000', 'lax $'),
        JSON_VALUE('1234567890123456789012345678901234', 'lax $.type()'));
VALUES JSON_VALUE('12345678901234567890123456789012345', 'lax $' ERROR ON ERROR);
VALUES '1e400' IS JSON;
                                                              |
--------------------------------------------------------------+--------------------------------------------------------------
 0.1                                                          | number
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22003): the number has more than 34 significant digits [ISO/IEC 9075-2:2023 §9.42 GR3a iii 3 A IV]
turborel: SQLCODE -301 (SQLSTATE 22003): the number is too great for an approximate number [ISO/IEC 9075-2:2023 §9.42 GR3a iii 3 A IV]

Errors

SQLSTATE SQLCODE When
22000 -301 % of an approximate number or one with a fraction
22003 -301 a number past its type's range, or of more than 34 significant digits
22012 -301 division by zero, / or %
2201S, 2201T -301 a like_regex pattern, or flags, that are none
2202V to 2202Z -301 a casting method of an item of a kind it does not take
22031 -301 datetime() of an item that is no string, or a string it does not read
22033 -301 a subscript that is not one number, or in strict mode outside the array
22036 -301 a numeric method or operator of an item that is no number
22038 -301 an operand of arithmetic that is not one item
22039 -301 an array accessor, or in strict mode size(), of an item that is no array
2203A -301 in strict mode, a member accessor of an item that is no object with the member
2203B -301 a sign of an item that is no number
2203C -301 in strict mode .*, or keyvalue(), of an item that is no object
2A000 -103 a variable PASSING does not give, or a path that is not one
-900 a path nested deeper than 64, or a number literal past the limits

Each exception condition of class 22 is one ON ERROR replaces.

Standard

The language is that of ISO/IEC 9075-2:2023 §9.46, with the item methods of Features T865 to T878 and keyvalue(), like_regex and starts with. Hexadecimal number literals are of §5.3. Its implementation-defined choices are these. number() keeps a number's own type. string() is at most 32,767 characters. keyvalue()'s id is each object's place among the items of the text and the variables, counted in the order they are written from 0, and an object the path makes is numbered past them.

See also

Value Expressions, Predicates, JSON data, SQLSTATEs