Re: remaining sql/json patches

public inbox for [email protected]  
help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Wed, 3 Jan 2024 18:53:34 +0800
Message-ID: <CACJufxEWqYusX6ab5wzCsztwRSaOnEGn55D16+r2O0XmWPZRtQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEY+yGb-sEMryoHesx++gEmWpXCYKti+ctjF1WAKmPh+g@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CA+HiwqFKhM=6fN3Np61uYGCsOGdBKk1K1MbCQ0Q6LMNWGShQ0A@mail.gmail.com>
	<[email protected]>
	<CA+HiwqFm4X39O6aGf6E-tSaSrd+p-Sg_S450fHZkspHdneO5qQ@mail.gmail.com>
	<CA+HiwqFTBYa+BipE3K=4--OC9f0-bK_n8Kh78Ez4fwrT78v=zQ@mail.gmail.com>
	<CACJufxEDfV97Kox67vYgkS5iuSd47j==Qt9n=TLRedDjYDJgsg@mail.gmail.com>
	<CACJufxEY+yGb-sEMryoHesx++gEmWpXCYKti+ctjF1WAKmPh+g@mail.gmail.com>

some more minor issues:
SELECT * FROM JSON_TABLE(jsonb '{"a":[123,2]}', '$'
    COLUMNS (item int[] PATH '$.a' error on error, foo text path '$'
error on error)) bar;
ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item

the error message seems not so great, imho.
since the JSON_TABLE doc entries didn't mention that
JSON_TABLE actually transformed to json_value, json_query, json_exists.

JSON_VALUE even though cannot specify KEEP | OMIT QUOTES.
It might be a good idea to mention the default is to omit quotes  in the doc.
because JSON_TABLE actually transformed to json_value, json_query, json_exists.
JSON_TABLE can specify quotes behavior freely.

bother again, i kind of get what the function transformJsonTableChildPlan do,
but adding more comments would make it easier to understand....

(json_query)
+        This function must return a JSON string, so if the path expression
+        returns multiple SQL/JSON items, you must wrap the result using the
+        <literal>WITH WRAPPER</literal> clause. 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, but if it is <literal>CONDITIONAL</literal>, it
will not be
+        applied to a single array or object. <literal>UNCONDITIONAL</literal>
+        is the default.  If the result is a scalar string, by default the value
+        returned will have surrounding quotes making it a valid JSON value,
+        which can be made explicit by specifying <literal>KEEP
QUOTES</literal>.
+        Conversely, quotes can be omitted by specifying <literal>OMIT
QUOTES</literal>.
+        The returned <replaceable>data_type</replaceable> has the
same semantics
+        as for constructor functions like <function>json_objectagg</function>;
+        the default returned type is <type>jsonb</type>.

+       <para>
+        Returns the result of applying the
+        <replaceable>path_expression</replaceable> to the
+        <replaceable>context_item</replaceable> using the
+        <literal>PASSING</literal> <replaceable>value</replaceable>s. The
+        extracted value must be a single <acronym>SQL/JSON</acronym> scalar
+        item. For results that are objects or arrays, use the
+        <function>json_query</function> function instead.
+        The returned <replaceable>data_type</replaceable> has the
same semantics
+        as for constructor functions like <function>json_objectagg</function>.
+        The default returned type is <type>text</type>.
+        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>

+        The returned <replaceable>data_type</replaceable> has the
same semantics
+        as for constructor functions like <function>json_objectagg</function>.

IMHO, the above description is not so good, since the function
json_objectagg is listed in functions-aggregate.html,
using Ctrl + F in the browser cannot find json_objectagg in functions-json.html.

for json_query, maybe we can rephrase like:
the RETURNING clause, which specifies the data type returned. It must
be a type for which there is a cast from text to that type.
By default, the <type>jsonb</type> type is returned.

json_value:
the RETURNING clause, which specifies the data type returned. It must
be a type for which there is a cast from text to that type.
By default, the <type>text</type> type is returned.

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: remaining sql/json patches
  In-Reply-To: <CACJufxEWqYusX6ab5wzCsztwRSaOnEGn55D16+r2O0XmWPZRtQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox