Re: [HACKERS] [PATCH] Generic type subscripting - Mailing list pgsql-hackers

On Thu Jan 14, 2021 at 10:04 AM EST, Dmitry Dolgov wrote:
> > On Tue, Jan 12, 2021 at 08:02:59PM +0100, Pavel Stehule wrote:
> > ne 10. 1. 2021 v 19:52 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
> > napsal:
> >
> > I tested behaviour and I didn't find anything other than the mentioned
> > issue.
> >
> > Now I can check this feature from plpgsql, and it is working. Because there
> > is no special support in plpgsql runtime, the update of jsonb is
> > significantly slower than in update of arrays, and looks so update of jsonb
> > has O(N2) cost. I don't think it is important at this moment - more
> > important is fact, so I didn't find any memory problems.
>
> Thanks for testing. Regarding updates when the structure doesn't match
> provided path as I've mentioned I don't have strong preferences, but on
> the second though probably more inclined for returning an error in this
> case. Since there are pros and cons for both suggestions, it could be
> decided by vote majority between no update (Dian) or an error (Pavel,
> me) options. Any +1 to one of the options from others?
>
> Other than that, since I've already posted the patch for returning an
> error option, it seems that the only thing left is to decide with which
> version to go.

The trigger issue (which I did verify) makes the "no update" option
unworkable imo, JavaScript's behavior notwithstanding. But it should be
called out very clearly in the documentation, since it does depart from
what people more familiar with that behavior may expect. Here's a quick
draft, based on your v44 patch:

<para>
 <type>jsonb</type> data type supports array-style subscripting expressions
 to extract or update particular elements. It's possible to use multiple
 subscripting expressions to extract nested values. In this case, a chain of
 subscripting expressions follows the same rules as the
 <literal>path</literal> argument in <literal>jsonb_set</literal> function,
 e.g. in case of arrays it is a 0-based operation or that negative integers
 that appear in <literal>path</literal> count from the end of JSON arrays.
 The result of subscripting expressions is always of the jsonb data type.
</para>
<para>
 <command>UPDATE</command> statements may use subscripting in the
 <literal>SET</literal> clause to modify <type>jsonb</type> values. Every
 affected value must conform to the path defined by the subscript(s). If the
 path cannot be followed to its end for any individual value (e.g.
 <literal>val['a']['b']['c']</literal> where <literal>val['a']</literal> or
 <literal>val['b']</literal> is null, a string, or a number), an error is
 raised even if other values do conform.
</para>
<para>
 An example of subscripting syntax: