v3-0001-SQL-JSON-Various-improvements-to-SQL-JSON-query-f.patch

From 6acf4bb58343eef3549c0a00c5eab1d8eea20de7 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 5 Jul 2024 21:31:15 +0900
Subject: [PATCH v3] SQL/JSON: Various improvements to SQL/JSON query function
 docs

1. Remove the keyword SELECT from the examples to be consistent
with the examples of other JSON-related functions listed on the
same page.

2. Add <synopsis> tags around the function's syntax definition.

3. Capitalize function names in the syntax synopsis and the examples.

4. Use <itemizedlist> lists for dividing the descriptions of individual
   functions into bullet points.

5. Significantly rewrite the description of wrapper clauses of
   JSON_QUERY

6. Significantly rewrite the descriptions of ON ERROR / EMPTY
   clauses

7. Add a note about how JSON_VALUE() and JSON_QUERY() differ when
   returning a JSON null result.

Suggested-by: Thom Brown <thom@linux.com>
Suggested-by: David G. Johnston <david.g.johnston@gmail.com>
Reviewed-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CAA-aLv7Dfy9BMrhUZ1skcg=OdqysWKzObS7XiDXdotJNF0E44Q@mail.gmail.com
---
 doc/src/sgml/func.sgml | 222 +++++++++++++++++++++++++++--------------
 1 file changed, 148 insertions(+), 74 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93ee3d4b60..0d6e3be2b1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18665,10 +18665,12 @@ $.* ? (@ like_regex "^\\d+$")
    <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>path_expression</replaceable> (a SQL/JSON path 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>context_item</replaceable> can be any character string that
+   can be succesfully cast to <type>jsonb</type>.
   </para>
 
   <table id="functions-sqljson-querying">
@@ -18691,37 +18693,56 @@ $.* ? (@ like_regex "^\\d+$")
      <row>
       <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>)
+<synopsis>
+<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>) <returnvalue>boolean</returnvalue>
+</synopsis>
        </para>
+     <itemizedlist>
+      <listitem>
        <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>.
+        <replaceable>path_expression</replaceable> can reference variables named
+        in the <literal>PASSING</literal> clause.
        </para>
+      </listitem>
+      <listitem>
        <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
-        <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.
+        an error occurs during <replaceable>path_expression</replaceable>
+        evaluation.  Specifying <literal>ERROR</literal> will cause an error to
+        be thrown with the appropriate message.  Other options include
+        returning <type>boolean</type> values <literal>FALSE</literal> or
+        <literal>TRUE</literal> or the value <literal>UNKNOWN</literal> which
+        is actually a SQL NULL. The default when no <literal>ON ERROR</literal>
+        clause is specified is to return the <type>boolean</type> value
+        <literal>FALSE</literal>.
        </para>
+      </listitem>
+      <listitem>
+       <para>
+        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.
+       </para>
+       </listitem>
+      </itemizedlist>
        <para>
         Examples:
        </para>
        <para>
-        <literal>select json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
+        <literal>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>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>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
@@ -18731,72 +18752,98 @@ 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> (
-        <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>)
+<synopsis>
+<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>) <returnvalue>jsonb</returnvalue>
+</synopsis>
       </para>
+     <itemizedlist>
+      <listitem>
        <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.
+        <replaceable>context_item</replaceable>.
+        <replaceable>path_expression</replaceable> can reference variables named
+        in the <literal>PASSING</literal> clause.
        </para>
+      </listitem>
+      <listitem>
        <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.
+         By default, the result is returned as a value of type <type>jsonb</type>,
+         though the <literal>RETURNING</literal> clause can be used to return
+         the original <type>jsonb</type> value as some other type to which it
+         can be successfully coerced.
        </para>
+      </listitem>
+      <listitem>
+       <para>
+        If the path expression may return multiple values, it might be necessary
+        to wrap those values using the <literal>WITH WRAPPER</literal> clause to
+        make it a valid JSON string, because the default behavior is to not wrap
+        them, as if <literal>WITHOUT WRAPPER</literal> were specified. The
+        <literal>WITH WRAPPER</literal> clause is by default taken to mean
+        <literal>WITH UNCONDITIONAL WRAPPER</literal>, which means that even a
+        single result value will be wrapped. To apply the wrapper only when
+        multiple values are present, specify <literal>WITH CONDITIONAL WRAPPER</literal>.
+        Note that an error will be thrown if multiple values are returned and
+        <literal>WITHOUT WRAPPER</literal> is specified.
+       </para>
+      </listitem>
+      <listitem>
        <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>.
+        To ensure that the result is a valid JSON value, <literal>OMIT QUOTES</literal>
+        cannot be specified when <literal>WITH WRAPPER</literal> is also
+        specified.
        </para>
+      </listitem>
+      <listitem>
        <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.
+        evaluating <replaceable>path_expression</replaceable> yields no value at
+        all. The <literal>ON ERROR</literal> clause specifies the behavior
+        behavior if an error occurs when evaluating
+        <replaceable>path_expression</replaceable>, when coercing the result
+        value to the <literal>RETURNING</literal> type, or when evaluating the
+        <literal>ON EMPTY</literal> expression (that is caused by empty result
+        of <replaceable>path_expression</replaceable>evaluation).
        </para>
+      </listitem>
+      <listitem>
        <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.
+        For both <literal>ON EMPTY</literal>and <literal>ON ERROR</literal>,
+        specifying <literal>ERROR</literal> will cause an error to be thrown with
+        the appropriate message. Other options include returning a SQL NULL, an
+        empty array or object (array by default), or a user-specified expression
+        that can be coerced to jsonb or the type specified in <literal>RETURNING</literal>.
+        The default when <literal>ON EMPTY</literal> or <literal>ON ERROR</literal>
+        is not specified is to return a SQL NULL value when the respective
+        situation occurs.
        </para>
+      </listitem>
+     </itemizedlist>
        <para>
         Examples:
        </para>
        <para>
-        <literal>select json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
+        <literal>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>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>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]"
@@ -18808,53 +18855,72 @@ 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> (
-        <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>)
+<synopsis>
+<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>) <returnvalue>text</returnvalue>
+</synopsis>
        </para>
+     <itemizedlist>
+      <listitem>
        <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.
+        <replaceable>context_item</replaceable>.
+        <replaceable>path_expression</replaceable> can reference variables named
+        in the <literal>PASSING</literal> clause.
        </para>
+      </listitem>
+      <listitem>
        <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.
+        Use <function>JSON_VALUE()</function> if the extracted value is expected
+        to be a single <acronym>SQL/JSON</acronym> scalar item; an error is
+        thrown if that's not the case (though see the discussion of
+        <literal>ON ERROR</literal> below). If you expect that extracted value
+        might be an object or an array, use the <function>JSON_QUERY</function>
+        function instead.
        </para>
+      </listitem>
+      <listitem>
        <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>.
+         By default, the result which must be a single scalar value is returned
+         as a value of type <type>text</type>, though the
+         <literal>RETURNING</literal> clause can be used to return the value as
+         some other type to which can be successfully coerced.
        </para>
+      </listitem>
+      <listitem>
        <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>.
+        <function>JSON_QUERY</function>, except the set of values returned in
+        lieu of throwing an error is different.
        </para>
+      </listitem>
+      <listitem>
        <para>
-        Note that scalar strings returned by <function>json_value</function>
+        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>.
+        <literal>OMIT QUOTES</literal> in <function>JSON_QUERY</function>.
        </para>
+      </listitem>
+     </itemizedlist>
        <para>
         Examples:
        </para>
        <para>
-        <literal>select json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
+        <literal>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&nbsp;YYYY-MM-DD")' RETURNING date)</literal>
+        <literal>JSON_VALUE(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI&nbsp;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>JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
         <returnvalue>9</returnvalue>
       </para></entry>
      </row>
@@ -18871,6 +18937,14 @@ DETAIL:  Missing "]" after array dimensions.
     clause.
    </para>
   </note>
+  <note>
+   <para>
+    <function>JSON_VALUE()</function> returns SQL NULL if
+    <replaceable>path_expression</replaceable> returns a JSON
+    <literal>null</literal>, whereas <function>JSON_QUERY()</function> returns
+    the JSON <literal>null</literal> as is.
+   </para>
+  </note>
   </sect2>
 
  <sect2 id="functions-sqljson-table">
-- 
2.43.0