Doc Rework: Section 9.16.13 SQL/JSON Query Functions
Hey!
Lots of SQL/JSON threads going about. This one is less about technical
correctness and more about usability of the documentation. Though in
writing this I am finding some things that aren't quite clear. I'm going
to come back with those on a follow-on post once I get a chance to make my
second pass on this. But for the moment just opening it up to a content
and structure review.
Please focus on the text changes. It passes "check-docs" but I still need
to work on layout and stuff in html (markup, some more links).
Thanks!
David J.
p.s. v1 exists here (is just the idea of using basically variable names in
the function signature and minimizing direct syntax in the table);
/messages/by-id/CAKFQuwbYBvUZasGj_ZnfXhC2kk4AT=epwGkNd2=RMMVXkfTNMQ@mail.gmail.com
Attachments:
v2-0001-doc-json-query_9-16-3.patchapplication/octet-stream; name=v2-0001-doc-json-query_9-16-3.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c324906b22..796f61138a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18668,7 +18668,27 @@ $.* ? (@ like_regex "^\\d+$")
<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.
+ <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.
</para>
<table id="functions-sqljson-querying">
@@ -18692,14 +18712,15 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_exists</primary></indexterm>
<function>json_exists</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
+ <replaceable>context_item</replaceable>,
+ <replaceable>path_expression</replaceable>
+ <optional>variable_definitions</optional>
+ <optional>on_error_boolean</optional>)
+ <returnvalue></returnvalue> <type>boolean</type>
</para>
<para>
Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
- applied to the <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s yields any
- items.
+ applied to the <replaceable>context_item</replaceable> yields any items.
</para>
<para>
The <literal>ON ERROR</literal> clause specifies the behavior if
@@ -18732,57 +18753,27 @@ ERROR: jsonpath array subscript is out of bounds
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_query</primary></indexterm>
<function>json_query</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
- <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ <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> }
</para>
<para>
Returns the result of applying the SQL/JSON
<replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s.
- </para>
- <para>
- If the path expression returns multiple SQL/JSON items, it might be
- necessary to wrap the result using the <literal>WITH WRAPPER</literal>
- clause to make it a valid JSON string. If the wrapper is
- <literal>UNCONDITIONAL</literal>, an array wrapper will always be
- applied, even if the returned value is already a single JSON object
- or an array. If it is <literal>CONDITIONAL</literal>, it will not be
- applied to a single JSON object or an array.
- <literal>UNCONDITIONAL</literal> is the default.
- </para>
- <para>
- 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 <literal>KEEP QUOTES</literal>. Conversely,
- quotes can be omitted by specifying <literal>OMIT QUOTES</literal>.
- Note that <literal>OMIT QUOTES</literal> cannot be specified when
- <literal>WITH WRAPPER</literal> is also specified.
- </para>
- <para>
- The <literal>RETURNING</literal> clause can be used to specify the
- <replaceable>data_type</replaceable> of the result value. By default,
- the returned value will be of type <type>jsonb</type>.
+ <replaceable>context_item</replaceable>.
</para>
<para>
- The <literal>ON EMPTY</literal> clause specifies the behavior if
- evaluating <replaceable>path_expression</replaceable> yields no value
- at all. The default when <literal>ON EMPTY</literal> is not specified
- is to return a null value.
- </para>
- <para>
- The <literal>ON ERROR</literal> clause specifies the
- behavior if an error occurs when evaluating
- <replaceable>path_expression</replaceable>, including the operation to
- coerce the result value to the output type, or during the execution of
- <literal>ON EMPTY</literal> behavior (that is caused by empty result
- of <replaceable>path_expression</replaceable> evaluation). The default
- when <literal>ON ERROR</literal> is not specified is to return a null
- value.
+ 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
+ the desired value as an SQL type.
</para>
<para>
Examples:
@@ -18809,17 +18800,19 @@ DETAIL: Missing "]" after array dimensions.
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_value</primary></indexterm>
<function>json_value</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
- <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ <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>)
+ <returnvalue></returnvalue> { <type>text</type> | <varname>return_data_type</varname> }
</para>
<para>
- Returns the result of applying the SQL/JSON
+ Returns the result of applying the SQL/JSON scalar-producing
<replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s.
+ <replaceable>context_item</replaceable> and, by default,
+ converting it to SQL <type>text</type>.
</para>
<para>
The extracted value must be a single <acronym>SQL/JSON</acronym>
@@ -18827,21 +18820,6 @@ DETAIL: Missing "]" after array dimensions.
that extracted value might be an object or an array, use the
<function>json_query</function> function instead.
</para>
- <para>
- The <literal>RETURNING</literal> clause can be used to specify the
- <replaceable>data_type</replaceable> of the result value. By default,
- the returned value will be of type <type>text</type>.
- </para>
- <para>
- The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
- clauses have similar semantics as mentioned in the description of
- <function>json_query</function>.
- </para>
- <para>
- Note that scalar strings returned by <function>json_value</function>
- always have their quotes removed, equivalent to specifying
- <literal>OMIT QUOTES</literal> in <function>json_query</function>.
- </para>
<para>
Examples:
</para>
@@ -18861,6 +18839,173 @@ DETAIL: Missing "]" after array dimensions.
</tbody>
</tgroup>
</table>
+
+ <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>
+ <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.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-return-clause">
+ <term><varname>return_clause</varname></term>
+ <listitem>
+ <para>
+ <optional>
+ RETURNING <replaceable>return_data_type</replaceable>
+ <optional>
+ <literal>FORMAT JSON</literal>
+ <optional>
+ <literal>ENCODING UTF8</literal>
+ </optional>
+ </optional>
+ </optional>
+ </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.
+ </para>
+ <para>
+ The optional format and encoding clauses may only be used if the <literal>data_type</literal> is an SQL character type.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-result-wrapping">
+ <term><varname>wrapping_clause</varname></term>
+ <listitem>
+ <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.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-result-quoting">
+ <term><varname>quoting_clause</varname></term>
+ <listitem>
+ <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.
+ </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"/>)
+ is not permitted.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-on-error-empty">
+ <term><varname>on_error_boolean</varname></term>
+ <term><varname>on_error_set</varname></term>
+ <term><varname>on_error_value</varname></term>
+ <term><varname>on_empty_set</varname></term>
+ <term><varname>on_empty_value</varname></term>
+ <listitem>
+ <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.
+ </para>
+ <para>
+ 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.
+ </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.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
</sect2>
<sect2 id="functions-sqljson-table">
Hi David,
On Tue, Jun 25, 2024 at 3:47 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
Hey!
Lots of SQL/JSON threads going about. This one is less about technical correctness and more about usability of the documentation. Though in writing this I am finding some things that aren't quite clear. I'm going to come back with those on a follow-on post once I get a chance to make my second pass on this. But for the moment just opening it up to a content and structure review.
Please focus on the text changes. It passes "check-docs" but I still need to work on layout and stuff in html (markup, some more links).
Thanks!
David J.
p.s. v1 exists here (is just the idea of using basically variable names in the function signature and minimizing direct syntax in the table);
/messages/by-id/CAKFQuwbYBvUZasGj_ZnfXhC2kk4AT=epwGkNd2=RMMVXkfTNMQ@mail.gmail.com
Thanks for writing the patch. I'll take a look at this next Monday.
--
Thanks, Amit Langote
On Fri, Jun 28, 2024 at 2:56 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Tue, Jun 25, 2024 at 3:47 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:Hey!
Lots of SQL/JSON threads going about. This one is less about technical correctness and more about usability of the documentation. Though in writing this I am finding some things that aren't quite clear. I'm going to come back with those on a follow-on post once I get a chance to make my second pass on this. But for the moment just opening it up to a content and structure review.
Please focus on the text changes. It passes "check-docs" but I still need to work on layout and stuff in html (markup, some more links).
Thanks!
David J.
p.s. v1 exists here (is just the idea of using basically variable names in the function signature and minimizing direct syntax in the table);
/messages/by-id/CAKFQuwbYBvUZasGj_ZnfXhC2kk4AT=epwGkNd2=RMMVXkfTNMQ@mail.gmail.com
Thanks for writing the patch. I'll take a look at this next Monday.
I've attached a delta (0002) against your patch, wherein I've kept
most of the structuring changes you've proposed, but made changes such
as:
* use tags consistently
* use language matching the rest of func.sgml, IMO
* avoid repetition (eg. context_item described both above and below the table)
* correcting some factual discrepancies (eg. json_value never returns json null)
* avoid forward references
* capitalize function names, SQL keywords in examples as requested in
a previous review [1]/messages/by-id/CAA-aLv7Dfy9BMrhUZ1skcg=OdqysWKzObS7XiDXdotJNF0E44Q@mail.gmail.com
Maybe we could still polish this some more.
--
Thanks, Amit Langote
[1]: /messages/by-id/CAA-aLv7Dfy9BMrhUZ1skcg=OdqysWKzObS7XiDXdotJNF0E44Q@mail.gmail.com
Attachments:
v2-0001-SQL-JSON-Improve-documentation-structure.patchapplication/octet-stream; name=v2-0001-SQL-JSON-Improve-documentation-structure.patchDownload
From 5f3d2c6bf760ef108d2db325ec5d24b63c3d8c74 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 2 Jul 2024 15:17:18 +0900
Subject: [PATCH v2 1/2] SQL/JSON: Improve documentation structure
Author: David G. Johnston <david.g.johnston@gmail.com>
Discussion: https://postgr.es/m/CAKFQuwZNxNHuPk44zDF7z8qZec1Aof10aA9tWvBU5CMhEKEd8A@mail.gmail.com
---
doc/src/sgml/func.sgml | 293 ++++++++++++++++++++++++++++++-----------
1 file changed, 219 insertions(+), 74 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..c6ee57c104 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18668,7 +18668,27 @@ $.* ? (@ like_regex "^\\d+$")
<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.
+ <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.
</para>
<table id="functions-sqljson-querying">
@@ -18692,14 +18712,15 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_exists</primary></indexterm>
<function>json_exists</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
+ <replaceable>context_item</replaceable>,
+ <replaceable>path_expression</replaceable>
+ <optional>variable_definitions</optional>
+ <optional>on_error_boolean</optional>)
+ <returnvalue></returnvalue> <type>boolean</type>
</para>
<para>
Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
- applied to the <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s yields any
- items.
+ applied to the <replaceable>context_item</replaceable> yields any items.
</para>
<para>
The <literal>ON ERROR</literal> clause specifies the behavior if
@@ -18732,57 +18753,27 @@ ERROR: jsonpath array subscript is out of bounds
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_query</primary></indexterm>
<function>json_query</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
- <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ <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> }
</para>
<para>
Returns the result of applying the SQL/JSON
<replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s.
- </para>
- <para>
- If the path expression returns multiple SQL/JSON items, it might be
- necessary to wrap the result using the <literal>WITH WRAPPER</literal>
- clause to make it a valid JSON string. If the wrapper is
- <literal>UNCONDITIONAL</literal>, an array wrapper will always be
- applied, even if the returned value is already a single JSON object
- or an array. If it is <literal>CONDITIONAL</literal>, it will not be
- applied to a single JSON object or an array.
- <literal>UNCONDITIONAL</literal> is the default.
- </para>
- <para>
- 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 <literal>KEEP QUOTES</literal>. Conversely,
- quotes can be omitted by specifying <literal>OMIT QUOTES</literal>.
- Note that <literal>OMIT QUOTES</literal> cannot be specified when
- <literal>WITH WRAPPER</literal> is also specified.
- </para>
- <para>
- The <literal>RETURNING</literal> clause can be used to specify the
- <replaceable>data_type</replaceable> of the result value. By default,
- the returned value will be of type <type>jsonb</type>.
+ <replaceable>context_item</replaceable>.
</para>
<para>
- The <literal>ON EMPTY</literal> clause specifies the behavior if
- evaluating <replaceable>path_expression</replaceable> yields no value
- at all. The default when <literal>ON EMPTY</literal> is not specified
- is to return a null value.
- </para>
- <para>
- The <literal>ON ERROR</literal> clause specifies the
- behavior if an error occurs when evaluating
- <replaceable>path_expression</replaceable>, including the operation to
- coerce the result value to the output type, or during the execution of
- <literal>ON EMPTY</literal> behavior (that is caused by empty result
- of <replaceable>path_expression</replaceable> evaluation). The default
- when <literal>ON ERROR</literal> is not specified is to return a null
- value.
+ 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
+ the desired value as an SQL type.
</para>
<para>
Examples:
@@ -18809,17 +18800,19 @@ DETAIL: Missing "]" after array dimensions.
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_value</primary></indexterm>
<function>json_value</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
- <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ <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>)
+ <returnvalue></returnvalue> { <type>text</type> | <varname>return_data_type</varname> }
</para>
<para>
- Returns the result of applying the SQL/JSON
+ Returns the result of applying the SQL/JSON scalar-producing
<replaceable>path_expression</replaceable> to the
- <replaceable>context_item</replaceable> using the
- <literal>PASSING</literal> <replaceable>value</replaceable>s.
+ <replaceable>context_item</replaceable> and, by default,
+ converting it to SQL <type>text</type>.
</para>
<para>
The extracted value must be a single <acronym>SQL/JSON</acronym>
@@ -18827,21 +18820,6 @@ DETAIL: Missing "]" after array dimensions.
that extracted value might be an object or an array, use the
<function>json_query</function> function instead.
</para>
- <para>
- The <literal>RETURNING</literal> clause can be used to specify the
- <replaceable>data_type</replaceable> of the result value. By default,
- the returned value will be of type <type>text</type>.
- </para>
- <para>
- The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
- clauses have similar semantics as mentioned in the description of
- <function>json_query</function>.
- </para>
- <para>
- Note that scalar strings returned by <function>json_value</function>
- always have their quotes removed, equivalent to specifying
- <literal>OMIT QUOTES</literal> in <function>json_query</function>.
- </para>
<para>
Examples:
</para>
@@ -18861,6 +18839,173 @@ DETAIL: Missing "]" after array dimensions.
</tbody>
</tgroup>
</table>
+
+ <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>
+ <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.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-return-clause">
+ <term><varname>return_clause</varname></term>
+ <listitem>
+ <para>
+ <optional>
+ RETURNING <replaceable>return_data_type</replaceable>
+ <optional>
+ <literal>FORMAT JSON</literal>
+ <optional>
+ <literal>ENCODING UTF8</literal>
+ </optional>
+ </optional>
+ </optional>
+ </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.
+ </para>
+ <para>
+ The optional format and encoding clauses may only be used if the <literal>data_type</literal> is an SQL character type.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-result-wrapping">
+ <term><varname>wrapping_clause</varname></term>
+ <listitem>
+ <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.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-result-quoting">
+ <term><varname>quoting_clause</varname></term>
+ <listitem>
+ <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.
+ </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"/>)
+ is not permitted.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="json-query-on-error-empty">
+ <term><varname>on_error_boolean</varname></term>
+ <term><varname>on_error_set</varname></term>
+ <term><varname>on_error_value</varname></term>
+ <term><varname>on_empty_set</varname></term>
+ <term><varname>on_empty_value</varname></term>
+ <listitem>
+ <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.
+ </para>
+ <para>
+ 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.
+ </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.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
<note>
<para>
The <replaceable>context_item</replaceable> expression is converted to
--
2.43.0
v2-0002-Delta-against-David-J-s-patch.patchapplication/octet-stream; name=v2-0002-Delta-against-David-J-s-patch.patchDownload
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
hi.
the following review is based on v2-0001, v2-0002.
"context_item can be a JSON document passed as a value of type json,
jsonb document, a character or an UTF8- endoded bytea string."
is wrong?
e.g. SELECT JSON_EXISTS( NULL::bytea, 'lax $.a[5]' ERROR ON ERROR)
check following query:
select oid, typtype , typname from pg_type where typcategory = 'S';
I think a more accurate description would be:
"context_item must be a JSON document passed as a value of type json,
jsonb document, a character string type(text, name, bpchar, varchar)"
do we need to mention domain over these types?
-------------------------------------
JSON_EXISTS
Returns true if the SQL/JSON path_expression possibly referencing the
variables in variable_definitions applied to the context_item yields
any items.
I am not native English speaker, so I found it hard to comprehend.
I can understand it like:
"Returns true if the SQL/JSON path_expression (possibly referencing
the variables in variable_definitions) applied to the context_item
yields any items."
maybe we can write it into two sentences, or
"Returns true if the SQL/JSON path_expression applied to the
context_item yields any items."
because you already mentioned "path_expression can also contain
variables whose values are specified using the variable_definitions
clause described below." in the top level.
-------------------------------------
The JSON_QUERY and JSON_VALUE functions are polymorphic in their
output type with the returning_clause clause dictating what that type
is.
how about
The JSON_QUERY and JSON_VALUE functions output type can be vary, using
returning_clause specify the desired data type.
-------------------------------------
your doc: JSON_VALUE "If path_expression points to a JSON null,
JSON_VALUE returns a SQL NULL."
`SELECT JSON_VALUE(jsonb 'null', '$');` here, the path_expression
points to '$' which is not json null?
so i like to change it to
"If the extracted value is a JSON null, an SQL NULL value will return."
-------------------------------------
inconsistency:
JSON_QUERY: <returnvalue></returnvalue> { <type>jsonb</type> |
<replaceable>return_data_type</replaceable> }
JSON_VALUE: <returnvalue></returnvalue> { <type>text</type> |
<varname>return_data_type</varname> }
-------------------------------------
{{For JSON_EXISTS (... on_error_boolean), alternative can be: ERROR,
UNKNOWN, TRUE, FALSE.
For JSON_QUERY (... on_error_set on_empty_set), alternative can be:
ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT followed by an
expression.
For JSON_VALUE (... on_error_set on_empty_set), alternative can be:
ERROR, NULL, or DEFAULT followed by an expression.
}}
i am not sure what does there dot means here, in the synopsis section,
three dots is significant.
Also if I understand it correctly, JSON_EXISTS can only have on_error,
then I am more confused with ``JSON_EXISTS (... on_error_boolean)``
Overall, I found this approach makes the synopsis scattered, it's not
easy to see the full picture.
for example:
```
JSON_VALUE ( context_item, path_expression [variable_definitions]
[return_type] [on_empty_value] [on_error_value]) → { text |
return_data_type }
```
this way it is not easy to find out that RETURNING is a keyword.
Currently in master, we can quickly see RETURNING is the keyword, the
master is kind of condense, though.
but if you are insistent with your approach, then that is fine for me.
still based on v2-0001, v2-0002.
picture attached.
as you can see from the curly braces,
```
{ KEEP | OMIT } QUOTES [ ON SCALAR STRING ]
```
we must choose one, "KEEP" or "OMIT".
but the wrapping_clause:
```
WITHOUT [ARRAY] WRAPPER
WITH [UNCONDITIONAL] [ARRAY] WRAPPER
WITH CONDITIONAL [ARRAY] WRAPPER
```
this way, didn't say we must choose between one in these three.
-----------
on_error_boolean
on_error_set
on_error_value
on_empty_set
on_empty_value
alternative ON { ERROR | EMPTY }
````
didn't explain on_error_value, on_empty_value.
why not just on_error_clause, on_empty_clause?
-------
<<<quoted paragraph
When JSON_QUERY 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 WITH CONDITIONAL variant to say that the wrapper
be added only when there are multiple values in the resulting array.
Or specify the WITHOUT variant to say that the wrapper be removed when
there is only one element, but it is ignored if there are multiple
values.
<<<quoted paragraph
The above paragraph didn't explicitly mention that UNCONDITIONAL is the default.
BTW, by comparing patch with master, I found out:
"""
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.
"""
this description seems not right.
if "UNCONDITIONAL is the default", then
select json_query(jsonb '{"a": [1]}', 'lax $.a' with unconditional
array wrapper);
should be same as
select json_query(jsonb '{"a": [1]}', 'lax $.a' );
another two examples with SQL/JSON scalar item:
select json_query(jsonb '{"a": 1}', 'lax $.a' );
select json_query(jsonb '{"a": 1}', 'lax $.a' with unconditional wrapper);
Am I interpreting "UNCONDITIONAL is the default" the wrong way?
Attachments:
Screenshot from 2024-07-04 16-35-33.pngimage/png; name="Screenshot from 2024-07-04 16-35-33.png"Download
�PNG
IHDR p � IL sBIT|d� tEXtSoftware gnome-screenshot��>