v2-0002-Delta-against-David-J-s-patch.patch
application/octet-stream
Filename: v2-0002-Delta-against-David-J-s-patch.patch
Type: application/octet-stream
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0002
Subject: Delta against David J's patch
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 155 | 177 |
From 98a10509f3982e4ef029148599842b0a101a62b6 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 2 Jul 2024 21:36:51 +0900
Subject: [PATCH v2 2/2] Delta against David J's patch
---
doc/src/sgml/func.sgml | 332 +++++++++++++++++++----------------------
1 file changed, 155 insertions(+), 177 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c6ee57c104..95dc98b302 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18658,37 +18658,22 @@ $.* ? (@ like_regex "^\\d+$")
</sect3>
</sect2>
- <sect2 id="sqljson-query-functions">
- <title>SQL/JSON Query Functions</title>
+ <sect2 id="sqljson-query-functions">
+ <title>SQL/JSON Query Functions</title>
<para>
SQL/JSON functions <literal>JSON_EXISTS()</literal>,
<literal>JSON_QUERY()</literal>, and <literal>JSON_VALUE()</literal>
described in <xref linkend="functions-sqljson-querying"/> can be used
to query JSON documents. Each of these functions apply a
<replaceable>path_expression</replaceable> (the query) to a
- <replaceable>context_item</replaceable> (the document); see
- <xref linkend="functions-sqljson-path"/> for more details on what
- <replaceable>path_expression</replaceable> can contain. Spoiler,
- it can contain variable names, and so there is a standard, optional,
- <literal>variable_definitions</literal> clause that can be used to
- passing in values for those variables.
- </para>
-
- <para>
- The different components of the SQL/JSON query function signatures are
- described following the table. Both the <function>json_query</function>
- and <function>json_value</function> functions are polymorphic in their
- return type, yielding the value indicated by default by able to
- be overridden by adding a <literal>return_clause</literal> or
- <literal>return_data_type</literal> clause respectively.
- </para>
-
- <para>
- The handling of errors originating from with the function
- (an invalid document is detected prior to function execution)
- as well as queries producing zero results, is controllable
- via the <literal>on_error_*</literal> and <literal>on_error_*</literal>
- clauses.
+ <replaceable>context_item</replaceable> (the document). Besides the
+ elements described in <xref linkend="functions-sqljson-path"/>,
+ <replaceable>path_expression</replaceable> can also contain variables
+ whose values are specified using the <literal>variable_definitions</literal>
+ clause described below. <replaceable>context_item</replaceable> can be
+ a JSON document passed as a value of type <type>json</type>,
+ <type>jsonb</type> document, a character or an <literal>UTF8</literal>-
+ endoded <type>bytea</type> string.
</para>
<table id="functions-sqljson-querying">
@@ -18711,38 +18696,40 @@ $.* ? (@ like_regex "^\\d+$")
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_exists</primary></indexterm>
- <function>json_exists</function> (
+ <function>JSON_EXISTS</function> (
<replaceable>context_item</replaceable>,
<replaceable>path_expression</replaceable>
- <optional>variable_definitions</optional>
- <optional>on_error_boolean</optional>)
+ <optional><replaceable>variable_definitions</replaceable></optional>
+ <optional><replaceable>on_error_boolean</replaceable></optional>)
<returnvalue></returnvalue> <type>boolean</type>
</para>
<para>
Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
+ possibly referencing the variables in <replaceable>variable_definitions</replaceable>
applied to the <replaceable>context_item</replaceable> yields any items.
</para>
<para>
- The <literal>ON ERROR</literal> clause specifies the behavior if
- an error occurs; the default is to return the <type>boolean</type>
- <literal>FALSE</literal> value. Note that if the
+ The <replaceable>on_error_boolean</replaceable> clause specifies the
+ behavior if an error occurs; the default is to return the
+ <type>boolean</type> <literal>FALSE</literal> value. Note that if the
<replaceable>path_expression</replaceable> is <literal>strict</literal>
- and <literal>ON ERROR</literal> behavior is <literal>ERROR</literal>,
- an error is generated if it yields no items.
+ and the value of <replaceable>on_error_boolean</replaceable> is
+ <literal>ERROR</literal>, an error is generated if
+ <replaceable>path_expression</replaceable> yields no items.
</para>
<para>
Examples:
</para>
<para>
- <literal>select json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
+ <literal>SELECT JSON_EXISTS(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
<returnvalue>t</returnvalue>
</para>
<para>
- <literal>select json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal>
+ <literal>SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal>
<returnvalue>f</returnvalue>
</para>
<para>
- <literal>select json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
+ <literal>SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
<returnvalue></returnvalue>
<programlisting>
ERROR: jsonpath array subscript is out of bounds
@@ -18752,46 +18739,62 @@ ERROR: jsonpath array subscript is out of bounds
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_query</primary></indexterm>
- <function>json_query</function> (
+ <function>JSON_QUERY</function> (
<replaceable>context_item</replaceable>,
<replaceable>path_expression</replaceable>
- <optional>variable_definitions</optional>
- <optional>return_clause</optional>
- <optional>wrapping_clause</optional>
- <optional>quoting_clause</optional>
- <optional>on_empty_set</optional>
- <optional>on_error_set</optional>)
- <returnvalue></returnvalue> { <type>jsonb</type> | <varname>return_data_type</varname> }
+ <optional><replaceable>variable_definitions</replaceable></optional>
+ <optional><replaceable>returning_clause</replaceable></optional>
+ <optional><replaceable>wrapping_clause</replaceable></optional>
+ <optional><replaceable>quoting_clause</replaceable></optional>
+ <optional><replaceable>on_empty_set</replaceable></optional>
+ <optional><replaceable>on_error_set</replaceable></optional>)
+ <returnvalue></returnvalue> { <type>jsonb</type> | <replaceable>return_data_type</replaceable> }
</para>
<para>
Returns the result of applying the SQL/JSON
- <replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable>.
+ <replaceable>path_expression</replaceable> possibly referencing
+ the variables in <replaceable>variable_definitions</replaceable>
+ to the <replaceable>context_item</replaceable>.
</para>
<para>
- By default, the output result will wrapped in an array.
- This is controlled by the wrapping_clause. Additionally,
- if only a single result is expected, the return_clause and
- possibly the quoting clauses, can be included to produce
+ By default, the output result will be wrapped in an array.
+ This is controlled by the <replaceable>wrapping_clause</replaceable>.
+ Additionally, if only a single result value is expected, the
+ <replaceable>returning_clause</replaceable> and possibly the
+ <replaceable>quoting_clause</replaceable> can be included to produce
the desired value as an SQL type.
</para>
+ <para>
+ If <replaceable>path_expression</replaceable> points to a JSON null,
+ <function>JSON_QUERY</function> returns a JSON null.
+ </para>
<para>
Examples:
</para>
<para>
- <literal>select json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
+ <literal>SELECT JSON_QUERY(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
<returnvalue>[3]</returnvalue>
</para>
<para>
- <literal>select json_query(jsonb '{"a": "[1, 2]"}', 'lax $.a' OMIT QUOTES);</literal>
+ <literal>SELECT JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' OMIT QUOTES);</literal>
<returnvalue>[1, 2]</returnvalue>
</para>
<para>
- <literal>select json_query(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR);</literal>
+ <literal>SELECT JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR);</literal>
<returnvalue></returnvalue>
<programlisting>
ERROR: malformed array literal: "[1, 2]"
DETAIL: Missing "]" after array dimensions.
+</programlisting>
+ </para>
+ <para>
+ <literal>SELECT JSON_QUERY(jsonb 'null', '$');</literal>
+ <returnvalue></returnvalue>
+<programlisting>
+ json_query
+------------
+ null
+(1 row)
</programlisting>
</para>
</entry>
@@ -18799,169 +18802,145 @@ DETAIL: Missing "]" after array dimensions.
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_value</primary></indexterm>
- <function>json_value</function> (
+ <function>JSON_VALUE</function> (
<replaceable>context_item</replaceable>,
<replaceable>path_expression</replaceable>
- <optional>variable_definitions</optional>
- <optional>return_type</optional>
- <optional>on_empty_value</optional>
- <optional>on_error_value</optional>)
+ <optional><replaceable>variable_definitions</replaceable></optional>
+ <optional><replaceable>return_type</replaceable></optional>
+ <optional><replaceable>on_empty_value</replaceable></optional>
+ <optional><replaceable>on_error_value</replaceable></optional>)
<returnvalue></returnvalue> { <type>text</type> | <varname>return_data_type</varname> }
</para>
<para>
- Returns the result of applying the SQL/JSON scalar-producing
- <replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable> and, by default,
- converting it to SQL <type>text</type>.
+ Returns the result of applying the SQL/JSON
+ <replaceable>path_expression</replaceable> possibly referencing
+ the variables in <replaceable>variable_definitions</replaceable>
+ to the <replaceable>context_item</replaceable>.
</para>
<para>
The extracted value must be a single <acronym>SQL/JSON</acronym>
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
- <function>json_query</function> function instead.
+ <function>JSON_QUERY</function> function instead.
+ </para>
+ <para>
+ If <replaceable>path_expression</replaceable> points to a JSON null,
+ <function>JSON_VALUE</function> returns a SQL NULL.
</para>
<para>
Examples:
</para>
<para>
- <literal>select json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
+ <literal>SELECT JSON_VALUE(jsonb '"123.45"', '$' RETURNING float)</literal>
<returnvalue>123.45</returnvalue>
</para>
<para>
- <literal>select json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
+ <literal>SELECT JSON_VALUE(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
<returnvalue>2015-02-01</returnvalue>
</para>
<para>
- <literal>select json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
+ <literal>SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
<returnvalue>9</returnvalue>
- </para></entry>
+ </para>
+ <para>
+ <literal>SELECT JSON_VALUE(jsonb 'null', '$');</literal>
+ <returnvalue></returnvalue>
+<programlisting>
+ json_query
+------------
+
+(1 row)
+</programlisting>
+ </para>
+ </entry>
</row>
</tbody>
</tgroup>
</table>
+ <para>
+ Elements besides <replaceable>context_item</replaceable> and
+ <replaceable>path_expression</replaceable> of the SQL/JSON query function
+ signatures are described below:
+ </para>
+
<variablelist>
- <varlistentry id="json-query-context-item">
- <term><varname>context_item</varname></term>
- <listitem>
- <para>
- <literal>castable_expression</literal>
- </para>
- <para>
- A value expression capable of being cast to the <type>jsonb</type> type.
- Failure during casting will result in function execution failure, regardless
- of the setting for the <literal>ON ERROR</literal> clause.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry id="json-query-path-expression">
- <term><varname>path_expression</varname></term>
- <listitem>
- <para>
- <literal>jsonpath_expression</literal>
- </para>
- <para>
- A value expression of type jsonpath. See each function's description for
- details regarding restrictions on what application of the jsonpath expression
- to the context_item can produce.
- </para>
- </listitem>
- </varlistentry>
<varlistentry id="json-query-variable-definitons">
<term><varname>variable_definitions</varname></term>
<listitem>
+<synopsis>
+ <literal>PASSING</literal> {<replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable>} <optional>, ...</optional>
+</synopsis>
<para>
- <optional>
- <literal>PASSING</literal> {
- <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable>
- }
- <optional>, ...</optional>
- </optional>
- </para>
- <para>
- If the <literal>path_expression</literal> contains JSONPath variable expression write
- <literal>PASSING</literal> after the expression and then provide a comma-separated
- list of values, mapped to names using the <literal>AS</literal> keyword.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry id="json-query-return-type">
- <term><varname>return_data_type</varname></term>
- <listitem>
- <para>
- <optional> data_type_identifier </optional>
- </para>
- <para>
- The <function>json_value</function> function expects to compute a scalar JSON value.
- This clause declares what SQL-scoped data type should actually be returned as the result.
- The default is <type>text</type>, with the JSON null value being converted to an SQL null value.
- You may specify <type>jsonb</type> or <type>json</type>, in which case the computed scalar result
- will be returned as-is, including the JSON null value.
+ A comma separated list of values with names specified with the
+ <literal>AS</literal> clause that correspond to the jsonpath variables
+ mentioned in the <literal>path_expression</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry id="json-query-return-clause">
- <term><varname>return_clause</varname></term>
+ <term><varname>returning_clause</varname></term>
<listitem>
+<synopsis>
+ RETURNING <replaceable>return_data_type</replaceable> <optional><literal>FORMAT JSON</literal><optional><literal>ENCODING UTF8</literal></optional></optional>
+</synopsis>
<para>
- <optional>
- RETURNING <replaceable>return_data_type</replaceable>
- <optional>
- <literal>FORMAT JSON</literal>
- <optional>
- <literal>ENCODING UTF8</literal>
- </optional>
- </optional>
- </optional>
+ The <function>JSON_QUERY</function> and <function>JSON_VALUE</function>
+ functions are polymorphic in their output type with the
+ <replaceable>returning_clause</replaceable> clause dictating what that
+ type is.
</para>
<para>
- The <function>json_query</function> function is polymorphic in its output type with this return_clause
- clause dictating what that type is. The default is <type>jsonb</type> in <literal>UTF8</literal> encoding.
- This is what you receive if you omit the entire clause.
+ For <function>JSON_QUERY</function>, the default is
+ <type>jsonb</type> in <literal>UTF8</literal> encoding, which is what
+ you get if you omit the entire clause. The optional format and encoding
+ clauses may only be used if the <literal>return_data_type</literal> is an
+ SQL character type.
</para>
<para>
- The optional format and encoding clauses may only be used if the <literal>data_type</literal> is an SQL character type.
+ The <function>JSON_VALUE</function> function expects to compute a scalar
+ JSON value. It is returned by default as a value of type <type>text</type>
+ or of type <replaceable>return_data_type</replaceable> if specified.
+ Format and encoding clauses are not allowed
+ for <function>JSON_VALUE</function>.
</para>
</listitem>
</varlistentry>
<varlistentry id="json-query-result-wrapping">
<term><varname>wrapping_clause</varname></term>
<listitem>
+<synopsis>
+ <literal>WITHOUT</literal> <optional><literal>ARRAY</literal></optional> <literal>WRAPPER</literal>
+ <literal>WITH</literal> <optional><literal>UNCONDITIONAL</literal></optional> <optional><literal>ARRAY</literal></optional> <literal>WRAPPER</literal>
+ <literal>WITH</literal> <literal>CONDITIONAL</literal> <optional><literal>ARRAY</literal></optional> <literal>WRAPPER</literal>
+</synopsis>
<para>
- <optional> <literal>WITHOUT</literal> <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- <optional> <literal>WITH</literal><optional> <literal>UNCONDITIONAL</literal> </optional><optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- <optional> <literal>WITH</literal><literal>CONDITIONAL</literal><optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- </para>
- <para>
- The <function>json_query</function> function can produce multiple JSON values, either as an object or a set,
- as a result of evaluating the <literal>path_expression</literal>. The <literal>WITHOUT</literal>
- variant will simply return the result as-is, possibly resulting in a error in the set case. The default
- <literal>WITH</literal> variant will always generate an outer array wrapper around the result. To
- omit the wrapper if the result is already a single object or array add the <literal>CONDITIONAL</literal>
- keyword after <literal>WITH</literal>.
- </para>
- <para>
- The default behavior is to unconditionally wrap the result in an array.
+ When <function>JSON_QUERY</function> function produces multiple JSON
+ values, they are returned as a JSON array. By default, the result values
+ are unconditionally wrapped even if the array contains only one element.
+ You can specify the <literal>WITH CONDITIONAL</literal> variant to say
+ that the wrapper be added only when there are multiple values in the
+ resulting array. Or specify the <literal>WITHOUT</literal> variant to
+ say that the wrapper be removed when there is only one element, but it
+ is ignored if there are multiple values.
</para>
</listitem>
</varlistentry>
<varlistentry id="json-query-result-quoting">
<term><varname>quoting_clause</varname></term>
<listitem>
+<synopsis>
+{ <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> ON SCALAR STRING </optional>
+</synopsis>
<para>
- <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> ON SCALAR STRING </optional> </optional>
- </para>
- <para>
- The <function>json_query</function> function outputs <type>jsonb</type> values; therefore a single scalar text result
- will be quoted. Specify <literal>OMIT QUOTES</literal> to remove the quotes and thus producing a result that can
- be cast to <type>text</type>.
- </para>
- <para>
- The optional <literal>ON SCALAR STRING</literal> modifier represents the default behavior.
+ The <function>JSON_QUERY</function> function outputs <type>jsonb</type>
+ values; therefore a single scalar text result will be quoted by default.
+ You can specify <literal>OMIT QUOTES</literal> to remove the quotes.
</para>
<para>
- To ensure the construction of a valid JSON array the combination of omit quotes and producing an
- unconditional wrapper (which is the default, see <xref linkend="json-query-result-wrapping"/>)
+ To ensure the construction of a valid JSON array the combination of
+ <literal>OMIT QUOTES</literal> and <literal>WITH WRAPPER</literal>
+ (which is the default, see <xref linkend="json-query-result-wrapping"/>)
is not permitted.
</para>
</listitem>
@@ -18973,34 +18952,33 @@ DETAIL: Missing "]" after array dimensions.
<term><varname>on_empty_set</varname></term>
<term><varname>on_empty_value</varname></term>
<listitem>
+<synopsis>
+ <replaceable>alternative</replaceable> <literal>ON</literal> { <literal>ERROR</literal> | <literal>EMPTY</literal> }
+</synopsis>
<para>
- <optional>
- <replaceable>alternative</replaceable>
- <literal>ON</literal> { <literal>ERROR</literal> | <literal>EMPTY</literal> }
- </optional>
- </para>
- <para>
- These clauses all provide for an alternative behavior when the result of path_expression
- evaulation results in either an error or no results at all. The different clauses simply
- specify a different subset of options that matches the expectations described in the
- function definitions. Specify <literal>ERROR</literal> or <literal>EMPTY</literal> as
- appropriate to the clause you are writing.
+ These clauses all provide for an alternative behavior when the evaluation
+ of <replaceable>path_expression</replaceable> either results in an error
+ or produces an empty set. The <literal>ON ERROR</literal> behavior also
+ applies to errors that occur when enforcing the
+ <replaceable>returning_clause</replaceable>.
</para>
<para>
- For <function>json_exists</function> (<literal>on_error_boolean</literal>),
+ For <function>JSON_EXISTS</function> (... <literal>on_error_boolean</literal>),
<literal>alternative</literal> can be: <literal>ERROR</literal>, <literal>UNKNOWN</literal>,
<literal>TRUE</literal>, <literal>FALSE</literal>.
</para>
<para>
- For <function>json_query</function> (<literal>on_error_set, and on_empty_set</literal>),
- <literal>alternative</literal> can be: <literal>ERROR</literal>, <literal>NULL</literal>,
- <literal>EMPTY ARRAY</literal>, <literal>EMPTY OBJECT</literal>, or <literal>DEFAULT</literal>
- followed by an expression.
+ For <function>JSON_QUERY</function> (... <literal>on_error_set</literal>
+ <literal>on_empty_set</literal>), <literal>alternative</literal> can be:
+ <literal>ERROR</literal>, <literal>NULL</literal>, <literal>EMPTY ARRAY</literal>,
+ <literal>EMPTY OBJECT</literal>, or <literal>DEFAULT</literal> followed by an
+ expression.
</para>
<para>
- For <function>json_value</function> (<literal>on_error_value, and on_empty_value</literal>),
- <literal>alternative</literal> can be: <literal>ERROR</literal>, <literal>NULL</literal>,
- or <literal>DEFAULT</literal> followed by an expression.
+ For <function>JSON_VALUE</function> (... <literal>on_error_set</literal>
+ <literal>on_empty_set</literal>), <literal>alternative</literal> can be:
+ <literal>ERROR</literal>, <literal>NULL</literal>, or <literal>DEFAULT</literal>
+ followed by an expression.
</para>
</listitem>
</varlistentry>
--
2.43.0