From 6185c9737cf48c9540782d88f12bd2912d6ca1cc Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Thu, 21 Mar 2024 17:06:27 +0900 Subject: Add SQL/JSON query functions This introduces the following SQL/JSON functions for querying JSON data using jsonpath expressions: JSON_EXISTS(), which can be used to apply a jsonpath expression to a JSON value to check if it yields any values. JSON_QUERY(), which can be used to to apply a jsonpath expression to a JSON value to get a JSON object, an array, or a string. There are various options to control whether multi-value result uses array wrappers and whether the singleton scalar strings are quoted or not. JSON_VALUE(), which can be used to apply a jsonpath expression to a JSON value to return a single scalar value, producing an error if it multiple values are matched. Both JSON_VALUE() and JSON_QUERY() functions have options for handling EMPTY and ERROR conditions, which can be used to specify the behavior when no values are matched and when an error occurs during jsonpath evaluation, respectively. Author: Nikita Glukhov Author: Teodor Sigaev Author: Oleg Bartunov Author: Alexander Korotkov Author: Andrew Dunstan Author: Amit Langote Author: Peter Eisentraut Author: Jian He Reviewers have included (in no particular order): Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, Jian He, Anton A. Melnikov, Nikita Malakhov, Peter Eisentraut, Tomas Vondra Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- doc/src/sgml/func.sgml | 210 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 210 insertions(+) (limited to 'doc/src/sgml/func.sgml') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 030ea8affdf..8ecc02f2b90 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15488,6 +15488,11 @@ table2-mapping the SQL/JSON path language + + + the SQL/JSON query functions + + @@ -18616,6 +18621,211 @@ $.* ? (@ like_regex "^\\d+$") + + + SQL/JSON Query Functions + + SQL/JSON functions JSON_EXISTS(), + JSON_QUERY(), and JSON_VALUE() + described in can be used + to query JSON documents. Each of these functions apply a + path_expression (the query) to a + context_item (the document); see + for more details on what + path_expression can contain. + + + + SQL/JSON Query Functions + + + + + Function signature + + + Description + + + Example(s) + + + + + + + json_exists + json_exists ( + context_item, path_expression PASSING { value AS varname } , ... + { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ) + + + Returns true if the SQL/JSON path_expression + applied to the context_item using the + PASSING values yields any + items. + + + The ON ERROR clause specifies the behavior if + an error occurs; the default is to return the boolean + FALSE value. Note that if the + path_expression is strict + and ON ERROR behavior is ERROR, + an error is generated if it yields no items. + + + Examples: + + + select json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)') + t + + + select json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR) + f + + + select json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR) + + +ERROR: jsonpath array subscript is out of bounds + + + + + + json_query + json_query ( + context_item, path_expression PASSING { value AS varname } , ... + RETURNING data_type FORMAT JSON ENCODING UTF8 + { WITHOUT | WITH { CONDITIONAL | UNCONDITIONAL } } ARRAY WRAPPER + { KEEP | OMIT } QUOTES ON SCALAR STRING + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON EMPTY + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR ) + + + Returns the result of applying the SQL/JSON + path_expression to the + context_item using the + PASSING values. + + + If the path expression returns multiple SQL/JSON items, it might be + necessary to wrap the result using the WITH WRAPPER + clause to make it a valid JSON string. If the wrapper is + UNCONDITIONAL, an array wrapper will always be + applied, even if the returned value is already a single JSON object + or an array. If it is CONDITIONAL, it will not be + applied to a single JSON object or an array. + UNCONDITIONAL is the default. + + + If the result is a scalar string, by default, the returned value will + be surrounded by quotes, making it a valid JSON value. It can be made + explicit by specifying KEEP QUOTES. Conversely, + quotes can be omitted by specifying OMIT QUOTES. + Note that OMIT QUOTES cannot be specified when + WITH WRAPPER is also specified. + + + The RETURNING clause can be used to specify the + data_type of the result value. By default, + the returned value will be of type jsonb. + + + The ON EMPTY clause specifies the behavior if + evaluating path_expression yields no value + at all. The default when ON EMPTY is not specified + is to return a null value. + + + The ON ERROR clause specifies the + behavior if an error occurs when evaluating + path_expression, including the operation to + coerce the result value to the output type, or during the execution of + ON EMPTY behavior (that is caused by empty result + of path_expression evaluation). The default + when ON ERROR is not specified is to return a null + value. + + + Examples: + + + select json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER) + [3] + + + select json_query(jsonb '{"a": "[1, 2]"}', 'lax $.a' OMIT QUOTES); + [1, 2] + + + select json_query(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR); + + +ERROR: malformed array literal: "[1, 2]" +DETAIL: Missing "]" after array dimensions. + + + + + + + json_value + json_value ( + context_item, path_expression + PASSING { value AS varname } , ... + RETURNING data_type + { ERROR | NULL | DEFAULT expression } ON EMPTY + { ERROR | NULL | DEFAULT expression } ON ERROR ) + + + Returns the result of applying the SQL/JSON + path_expression to the + context_item using the + PASSING values. + + + The extracted value must be a single SQL/JSON + scalar item; an error is thrown if that's not the case. If you expect + that extracted value might be an object or an array, use the + json_query function instead. + + + The RETURNING clause can be used to specify the + data_type of the result value. By default, + the returned value will be of type text. + + + The ON ERROR and ON EMPTY + clauses have similar semantics as mentioned in the description of + json_query. + + + Note that scalar strings returned by json_value + always have their quotes removed, equivalent to specifying + OMIT QUOTES in json_query. + + + Examples: + + + select json_value(jsonb '"123.45"', '$' RETURNING float) + 123.45 + + + select json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date) + 2015-02-01 + + + select json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR) + 9 + + + + +
+ -- cgit v1.2.3