From 99522126628aba105b16f7ac1c19fd41af955ac3 Mon Sep 17 00:00:00 2001 From: jian he Date: Sat, 20 Jan 2024 20:21:01 +0800 Subject: [PATCH v35 1/1] refactor json_value, json_query, json_exists doc --- doc/src/sgml/func.sgml | 180 ++++++++++++++++++++--------------------- 1 file changed, 88 insertions(+), 92 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 0481490f..67aa4229 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15461,6 +15461,11 @@ table2-mapping the SQL/JSON path language + + + SQL/JSON query functions + + @@ -18162,89 +18167,81 @@ $.* ? (@ like_regex "^\\d+$") + + + + SQL/JSON query functions + + + SQL/JSON query functions + - - SQL/JSON Query Functions - details the SQL/JSON - functions that can be used to query JSON data. + These SQL/JSON functions: (json_exists, json_query, json_value) can be used to query JSON data. + All these functions applying the path_expression to the context_item. + See detail explanation of the path_expression in . - SQL/JSON path expression can currently only accept values of the + SQL/JSON query functions can currently only accept values of the jsonb type, so it might be necessary to cast the context_item argument of these functions to jsonb. + + json_exists has the syntax: + - - SQL/JSON Query Functions - - - - - Function signature - + +json_exists( +context_item, path_expression PASSING { value AS varname } , ... + { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ) + - 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 + json_exists returns true if the SQL/JSON path_expression applied to the context_item using the 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, an error is generated if it yields no - items, provided the specified ON ERROR behavior is - ERROR. + is strict, ON ERROR behavior is + specified ERROR, an error is generated if it yields no items. + + + Some examples: + +json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)') t + +json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR) f + +json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR) ERROR: jsonpath array subscript is out of bounds + + + + +json_query has the syntax: + + + +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 ) + + - json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)') - t - - - json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR) - f - - - 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 + json_query returns the result of applying the path_expression to the context_item using the values. - This function must return a JSON string, so if the path expression + This function must return a JSON string, if the path expression returns multiple SQL/JSON items, you must wrap the result using the WITH WRAPPER clause. If the wrapper is UNCONDITIONAL, an array wrapper will always @@ -18270,23 +18267,29 @@ $.* ? (@ like_regex "^\\d+$") jsonpath evaluation); the default when ON ERROR is not specified is to return a null value. + + + example: + +json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER) [3] + + + + +json_value has the syntax: + + + +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 ) + + - json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER) - [3] - - - - - 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 + json_value returns the result of applying the path_expression to the context_item using the PASSING values. The @@ -18306,24 +18309,17 @@ $.* ? (@ like_regex "^\\d+$") equivalent to what one would get with OMIT QUOTES when using json_query. - - json_value(jsonb '"123.45"', '$' RETURNING float) - 123.45 - - - json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date) - 2015-02-01 - - - json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR) - 9 - - - - -
- + + Some examples: + +json_value(jsonb '"123.45"', '$' RETURNING float) 123.45 + +json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date) 2015-02-01 + +json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR) 9 + + -- 2.34.1