diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cc72b9c2f6d..8614a26fe95 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17675,18 +17675,18 @@ $.* ? (@ like_regex "^\\d+$") json constructor json ( - expression + expression FORMAT JSON ENCODING UTF8 { WITH | WITHOUT } UNIQUE KEYS RETURNING json_data_type ) - The expression can be any text type or a + The expression can be any text type or a bytea in UTF8 encoding. If the - expression is NULL, an + expression is NULL, an SQL null value is returned. If WITH UNIQUE is specified, the - expression must not contain any duplicate + expression must not contain any duplicate object keys. @@ -17701,12 +17701,12 @@ $.* ? (@ like_regex "^\\d+$") json_scalar - json_scalar (expression + json_scalar (expression RETURNING json_data_type ) Returns a JSON scalar value representing - expression. + expression. If the input is NULL, an SQL NULL is returned. If the input is a number or a boolean value, a corresponding JSON number or boolean value is returned. For any other value a JSON string is returned. @@ -17724,8 +17724,8 @@ $.* ? (@ like_regex "^\\d+$") json_object json_object ( - { key_expression { VALUE | ':' } - value_expression FORMAT JSON ENCODING UTF8 }, ... + { key_expression { VALUE | ':' } + value_expression FORMAT JSON ENCODING UTF8 }, ... { NULL | ABSENT } ON NULL { WITH | WITHOUT } UNIQUE KEYS RETURNING data_type FORMAT JSON ENCODING UTF8 ) @@ -17733,15 +17733,15 @@ $.* ? (@ like_regex "^\\d+$") Constructs a JSON object of all the key value pairs given, or an empty object if none are given. - key_expression is a scalar expression + key_expression is a scalar expression defining the JSON key, which is converted to the text type. It cannot be NULL nor can it belong to a type that has a cast to the json. If WITH UNIQUE is specified, there must not - be any duplicate key_expression. + be any duplicate key_expression. If ABSENT ON NULL is specified, the entire - pair is omitted if the value_expression + pair is omitted if the value_expression is NULL. @@ -17753,7 +17753,7 @@ $.* ? (@ like_regex "^\\d+$") json_objectagg json_objectagg ( - { key_expression { VALUE | ':' } value_expression } + { key_expression { VALUE | ':' } value_expression } { NULL | ABSENT } ON NULL { WITH | WITHOUT } UNIQUE KEYS RETURNING data_type FORMAT JSON ENCODING UTF8 ) @@ -17761,8 +17761,8 @@ $.* ? (@ like_regex "^\\d+$") Behaves like json_object above, but as an aggregate function, so it only takes one - key_expression and one - value_expression parameter. + key_expression and one + value_expression parameter. SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v) @@ -17773,7 +17773,7 @@ $.* ? (@ like_regex "^\\d+$") json_array json_array ( - { value_expression FORMAT JSON } , ... + { value_expression FORMAT JSON } , ... { NULL | ABSENT } ON NULL RETURNING data_type FORMAT JSON ENCODING UTF8 ) @@ -17784,7 +17784,7 @@ $.* ? (@ like_regex "^\\d+$") Constructs a JSON array from either a series of - value_expression parameters or from the results + value_expression parameters or from the results of query_expression, which must be a SELECT query returning a single column. If ABSENT ON NULL is specified, NULL values are ignored. @@ -17804,7 +17804,7 @@ $.* ? (@ like_regex "^\\d+$") json_arrayagg json_arrayagg ( - value_expression + value_expression ORDER BY sort_expression { NULL | ABSENT } ON NULL RETURNING data_type FORMAT JSON ENCODING UTF8 ) @@ -17812,7 +17812,7 @@ $.* ? (@ like_regex "^\\d+$") Behaves in the same way as json_array but as an aggregate function so it only takes one - value_expression parameter. + value_expression parameter. If ABSENT ON NULL is specified, any NULL values are omitted. If ORDER BY is specified, the elements will @@ -17852,18 +17852,18 @@ $.* ? (@ like_regex "^\\d+$") IS JSON - expression IS NOT JSON + expression IS NOT JSON { VALUE | SCALAR | ARRAY | OBJECT } { WITH | WITHOUT } UNIQUE KEYS - This predicate tests whether expression can be + This predicate tests whether expression can be parsed as JSON, possibly of a specified type. If SCALAR or ARRAY or OBJECT is specified, the test is whether or not the JSON is of that particular type. If - WITH UNIQUE is specified, then an any object in the - expression is also tested to see if it + WITH UNIQUE is specified, then any object in the + expression is also tested to see if it has duplicate keys. @@ -17889,12 +17889,12 @@ FROM json_serialize ( - expression FORMAT JSON ENCODING UTF8 - RETURNING data_type FORMAT JSON ENCODING UTF8 ) + expression FORMAT JSON ENCODING UTF8 + RETURNING data_type FORMAT JSON ENCODING UTF8 ) Transforms an SQL/JSON value into a character or binary string. The - expression can be of any JSON type, any + expression can be of any JSON type, any character string type, or bytea in UTF8 encoding. The returned type can be any character string type or bytea. The default is text. @@ -17917,7 +17917,7 @@ FROM SQL/JSON paths can only be applied to the jsonb type, so it - might be necessary to cast the context_item + might be necessary to cast the context_item argument of these functions to jsonb. @@ -17943,16 +17943,16 @@ FROM json_exists json_exists ( - context_item, path_expression PASSING { value AS varname } , ... + context_item, path_expression PASSING { value AS varname } , ... RETURNING data_type { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ) - Returns true if the SQL/JSON path_expression - applied to the context_item using the - values yields any items. + Returns true if the SQL/JSON path_expression + applied to the context_item using the + values yields any items. The ON ERROR clause specifies what is returned if - an error occurs. Note that if the path_expression + an error occurs. Note that if the path_expression is strict, an error is generated if it yields no items. The default value is UNKNOWN which causes a NULL result. @@ -17974,28 +17974,30 @@ FROM 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 ) + 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 - path_expression to the - context_item using the - values. The extracted value must be + path_expression to the + context_item using the + values. The extracted value must be a single SQL/JSON scalar item. For results that are objects or arrays, use the json_query - instead. - The returned data_type has the same semantics + function instead. + The returned data_type has the same semantics as for constructor functions like json_objectagg. The default returned type is text. The ON EMPTY clause specifies the behavior if the - path_expression yields no value at all. + path_expression yields no value at all. The ON ERROR clause specifies the behavior if an - error occurs, as a result of either the evaluation or the application - of the ON EMPTY clause. + error occurs as a result of jsonpath evaluation + (including cast to the output type) or execution of + ON EMPTY behavior (that was caused by empty result + of jsonpath evaluation). json_value(jsonb '"123.45"', '$' RETURNING float) @@ -18014,24 +18016,24 @@ FROM json_query json_query ( - context_item, path_expression PASSING { value AS varname } , ... - RETURNING data_type FORMAT JSON ENCODING UTF8 + 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 ) + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON EMPTY + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR ) Returns the result of applying the - path_expression to the - context_item using the - values. + path_expression to the + context_item using the + values. This function must return a JSON string, so 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 be applied, even if the returned value is already a single JSON object - or array, but if it is CONDITIONAL it will not be + or array, but if it is CONDITIONAL, it will not be applied to a single array or object. UNCONDITIONAL is the default. If the result is a scalar string, by default the value returned will have @@ -18040,7 +18042,7 @@ FROM The ON ERROR and ON EMPTY clauses have similar semantics to those clauses for json_value. - The returned data_type has the same semantics + The returned data_type has the same semantics as for constructor functions like json_objectagg. The default returned type is text. @@ -18105,7 +18107,7 @@ FROM columns. Columns produced by NESTED PATHs at the same level are considered to be siblings, while a column produced by a NESTED PATH is - considered to be a child of the column produced by and + considered to be a child of the column produced by a NESTED PATH or row expression at a higher level. Sibling columns are always joined first. Once they are processed, the resulting rows are joined to the parent row. @@ -18114,13 +18116,13 @@ FROM - context_item, path_expression AS json_path_name PASSING { value AS varname } , ... + context_item, path_expression AS json_path_name PASSING { value AS varname } , ... The input data to query, the JSON path expression defining the query, and an optional PASSING clause, which can provide data - values to the path_expression. + values to the path_expression. The result of the input data evaluation is called the row pattern. The row pattern is used as the source for row values in the constructed view. @@ -18130,7 +18132,7 @@ FROM - COLUMNS( json_table_column , ... ) + COLUMNS( json_table_column , ... ) @@ -18138,15 +18140,15 @@ FROM The COLUMNS clause defining the schema of the constructed view. In this clause, you must specify all the columns to be filled with SQL/JSON items. - The json_table_column + The json_table_column expression has the following syntax variants: - name type - PATH json_path_specification + name type + PATH json_path_specification @@ -18156,7 +18158,7 @@ FROM The provided PATH expression parses the - row pattern defined by json_api_common_syntax + row pattern defined by json_api_common_syntax and fills the column with produced SQL/JSON items, one for each row. If the PATH expression is omitted, JSON_TABLE uses the @@ -18179,8 +18181,8 @@ FROM - name type FORMAT json_representation - PATH json_path_specification + name type FORMAT json_representation + PATH json_path_specification @@ -18190,12 +18192,12 @@ FROM The provided PATH expression parses the - row pattern defined by json_api_common_syntax + row pattern defined by json_api_common_syntax and fills the column with produced SQL/JSON items, one for each row. If the PATH expression is omitted, JSON_TABLE uses the - $.name path expression, - where name is the provided column name. + $.name path expression, + where name is the provided column name. In this case, the column name must correspond to one of the keys within the SQL/JSON item produced by the row pattern. @@ -18211,8 +18213,8 @@ FROM - name type - EXISTS PATH json_path_specification + name type + EXISTS PATH json_path_specification @@ -18221,10 +18223,10 @@ FROM The provided PATH expression parses the - row pattern defined by json_api_common_syntax, + row pattern defined by json_api_common_syntax, checks whether any SQL/JSON items were returned, and fills the column with resulting boolean value, one for each row. - The specified type should have cast from + The specified type should have cast from boolean. If the PATH expression is omitted, JSON_TABLE uses the @@ -18241,8 +18243,8 @@ FROM - NESTED PATH json_path_specification AS json_path_name - COLUMNS ( json_table_column , ... ) + NESTED PATH json_path_specification AS json_path_name + COLUMNS ( json_table_column , ... ) @@ -18250,7 +18252,7 @@ FROM Extracts SQL/JSON items from nested levels of the row pattern, generates one or more columns as defined by the COLUMNS subclause, and inserts the extracted SQL/JSON items into each row of these columns. - The json_table_column expression in the + The json_table_column expression in the COLUMNS subclause uses the same syntax as in the parent COLUMNS clause. @@ -18266,14 +18268,14 @@ FROM You can use the PLAN clause to define how - to join the columns returned by NESTED PATH clauses. + to join the columns returned by NESTED PATH clauses. - name FOR ORDINALITY + name FOR ORDINALITY @@ -18292,13 +18294,13 @@ FROM - AS json_path_name + AS json_path_name - The optional json_path_name serves as an - identifier of the provided json_path_specification. + The optional json_path_name serves as an + identifier of the provided json_path_specification. The path name must be unique and distinct from the column names. When using the PLAN clause, you must specify the names for all the paths, including the row pattern. Each path name can appear in @@ -18309,7 +18311,7 @@ FROM - PLAN ( json_table_plan ) + PLAN ( json_table_plan ) @@ -18395,7 +18397,7 @@ FROM - PLAN DEFAULT ( OUTER | INNER , UNION | CROSS ) + PLAN DEFAULT ( OUTER | INNER , UNION | CROSS )