From: Dian M Fay <dian.m.fay@gmail.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@anarazel.de>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: David Steele <david@pgmasters.net>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: David Fetter <david@fetter.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Oleg Bartunov <obartunov@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
Date: Wed, 20 Jan 2021 23:37:32 -0500
Message-ID: <C8OK3JQNE1KN.2H0O0RCBD576E@lamia> (raw)
In-Reply-To: <20210120190857.vvtwtabubwxzeraq@localhost>
On Wed Jan 20, 2021 at 2:08 PM EST, Dmitry Dolgov wrote:
> > On Wed, Jan 20, 2021 at 11:34:16AM -0500, Dian M Fay wrote:
> > > Thanks, I need to remember to not skipp doc building for testing process
> > > even for such small changes. Hope now I didn't forget anything.
> > >
> > > > On Wed, Jan 20, 2021 at 09:58:43AM -0500, Dian M Fay wrote:
> > >
> > > > Here's a full editing pass on the documentation, with v45 and Pavel's
> > > > doc-whitespaces-fix.patch applied. I also corrected a typo in one of the
> > > > added hints.
> > >
> > > Great! I've applied almost all of it, except:
> > >
> > > + A <type>jsonb</type> value will accept assignments to nonexistent
> > > subscript
> > > + paths as long as the nonexistent elements being traversed are all
> > > arrays.
> > >
> > > Maybe I've misunderstood the intention, but there is no requirement
> > > about arrays for creating such an empty path. I've formulated it as:
> > >
> > > + A <type>jsonb</type> value will accept assignments to nonexistent
> > > subscript
> > > + paths as long as the last existing path key is an object or an array.
> >
> > My intention there was to highlight the difference between:
> >
> > * SET obj['a']['b']['c'] = '"newvalue"'
> > * SET arr[0][0][3] = '"newvalue"'
> >
> > obj has to conform to {"a": {"b": {...}}} in order to receive the
> > assignment of the nested c. If it doesn't, that's the error case we
> > discussed earlier. But arr can be null, [], and so on, and any missing
> > structure [[[null, null, null, "newvalue"]]] will be created.
>
> If arr is 'null', or any other scalar value, such subscripting will work
> only one level deep because they represented internally as an array of
> one element. If arr is '[]' the path will comply by definition. So it's
> essentially the same as for objects with no particular difference. If
> such a quirk about scalars being treated like arrays is bothering, we
> could also bend it in this case as well (see the attached version).
I missed that distinction in the original UPDATE paragraph too. Here's
another revision based on v48.
From a486ee221469037b08d3663f1ec142a905406f8b Mon Sep 17 00:00:00 2001
From: Dian M Fay <dian.m.fay@gmail.com>
Date: Wed, 20 Jan 2021 23:36:34 -0500
Subject: [PATCH] more jsonb subscripting documentation edits
---
doc/src/sgml/json.sgml | 40 ++++++++++++++++++++++------------------
1 file changed, 22 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgmlindex deeb9e66e0..e16dd6973d 100644--- a/doc/src/sgml/json.sgml+++ b/doc/src/sgml/json.sgml@@ -616,16 +616,17 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
<para>
<command>UPDATE</command> statements may use subscripting in the
- <literal>SET</literal> clause to modify <type>jsonb</type> values. Object- values being traversed must exist as specified by the subscript path. For- instance, the path <literal>val['a']['b']['c']</literal> assumes that- <literal>val</literal>, <literal>val['a']</literal>, and <literal>val['a']['b']</literal>- are all objects in every record being updated (<literal>val['a']['b']</literal>- may or may not contain a field named <literal>c</literal>, as long as it's an- object). If any individual <literal>val</literal>, <literal>val['a']</literal>,- or <literal>val['a']['b']</literal> is a non-object such as a string, a number,- or <literal>NULL</literal>, an error is raised even if other values do conform.- Array values are not subject to this restriction, as detailed below.+ <literal>SET</literal> clause to modify <type>jsonb</type> values. Subscript+ paths must be traversible for all affected values insofar as they exist. For+ instance, the path <literal>val['a']['b']['c']</literal> can be traversed all+ the way to <literal>c</literal> if every <literal>val</literal>,+ <literal>val['a']</literal>, and <literal>val['a']['b']</literal> is an+ object. If any <literal>val['a']</literal> or <literal>val['a']['b']</literal>+ is not defined, it will be created as an empty object and filled as+ necessary. However, if any <literal>val</literal> itself or one of the+ intermediary values is defined as a non-object such as a string, number, or+ <literal>jsonb</literal> <literal>null</literal>, traversal cannot proceed so+ an error is raised and the transaction aborted.
</para>
<para>
@@ -658,8 +659,9 @@ SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
<type>jsonb</type> assignment via subscripting handles a few edge cases
differently from <literal>jsonb_set</literal>. When a source <type>jsonb</type>
- is <literal>NULL</literal>, assignment via subscripting will proceed as if- it was an empty JSON object:+ value is <literal>NULL</literal>, assignment via subscripting will proceed+ as if it was an empty JSON value of the type (object or array) implied by the+ subscript key:
<programlisting>
-- Where jsonb_field was NULL, it is now {"a": 1}
@@ -680,17 +682,19 @@ UPDATE table_name SET jsonb_field[2] = '2';
</programlisting>
A <type>jsonb</type> value will accept assignments to nonexistent subscript
- paths as long as the last existing path key is an object or an array. Since- the final subscript is not traversed, it may be an object key. Nested arrays- will be created and <literal>NULL</literal>-padded according to the path until- the value can be placed appropriately.+ paths as long as the last existing element to be traversed is an object or+ array, as implied by the corresponding subscript (the element indicated by+ the last subscript in the path is not traversed and may be anything). Nested+ array and object structures will be created, and in the former case+ <literal>null</literal>-padded, as specified by the subscript path until the+ assigned value can be placed.
<programlisting>
-- Where jsonb_field was {}, it is now {'a': [{'b': 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
--- Where jsonb_field was [], it is now [{'a': 1}]-UPDATE table_name SET jsonb_field[0]['a'] = '1';+-- Where jsonb_field was [], it is now [null, {'a': 1}]+UPDATE table_name SET jsonb_field[1]['a'] = '1';
</programlisting>
</para>
--
2.30.0
Attachments:
[text/plain] 0001-more-jsonb-subscripting-documentation-edits.patch (4.1K, ../C8OK3JQNE1KN.2H0O0RCBD576E@lamia/2-0001-more-jsonb-subscripting-documentation-edits.patch)
download | inline diff:
From a486ee221469037b08d3663f1ec142a905406f8b Mon Sep 17 00:00:00 2001
From: Dian M Fay <dian.m.fay@gmail.com>
Date: Wed, 20 Jan 2021 23:36:34 -0500
Subject: [PATCH] more jsonb subscripting documentation edits
---
doc/src/sgml/json.sgml | 40 ++++++++++++++++++++++------------------
1 file changed, 22 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgmlindex deeb9e66e0..e16dd6973d 100644--- a/doc/src/sgml/json.sgml+++ b/doc/src/sgml/json.sgml@@ -616,16 +616,17 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
<para>
<command>UPDATE</command> statements may use subscripting in the
- <literal>SET</literal> clause to modify <type>jsonb</type> values. Object- values being traversed must exist as specified by the subscript path. For- instance, the path <literal>val['a']['b']['c']</literal> assumes that- <literal>val</literal>, <literal>val['a']</literal>, and <literal>val['a']['b']</literal>- are all objects in every record being updated (<literal>val['a']['b']</literal>- may or may not contain a field named <literal>c</literal>, as long as it's an- object). If any individual <literal>val</literal>, <literal>val['a']</literal>,- or <literal>val['a']['b']</literal> is a non-object such as a string, a number,- or <literal>NULL</literal>, an error is raised even if other values do conform.- Array values are not subject to this restriction, as detailed below.+ <literal>SET</literal> clause to modify <type>jsonb</type> values. Subscript+ paths must be traversible for all affected values insofar as they exist. For+ instance, the path <literal>val['a']['b']['c']</literal> can be traversed all+ the way to <literal>c</literal> if every <literal>val</literal>,+ <literal>val['a']</literal>, and <literal>val['a']['b']</literal> is an+ object. If any <literal>val['a']</literal> or <literal>val['a']['b']</literal>+ is not defined, it will be created as an empty object and filled as+ necessary. However, if any <literal>val</literal> itself or one of the+ intermediary values is defined as a non-object such as a string, number, or+ <literal>jsonb</literal> <literal>null</literal>, traversal cannot proceed so+ an error is raised and the transaction aborted.
</para>
<para>
@@ -658,8 +659,9 @@ SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
<type>jsonb</type> assignment via subscripting handles a few edge cases
differently from <literal>jsonb_set</literal>. When a source <type>jsonb</type>
- is <literal>NULL</literal>, assignment via subscripting will proceed as if- it was an empty JSON object:+ value is <literal>NULL</literal>, assignment via subscripting will proceed+ as if it was an empty JSON value of the type (object or array) implied by the+ subscript key:
<programlisting>
-- Where jsonb_field was NULL, it is now {"a": 1}
@@ -680,17 +682,19 @@ UPDATE table_name SET jsonb_field[2] = '2';
</programlisting>
A <type>jsonb</type> value will accept assignments to nonexistent subscript
- paths as long as the last existing path key is an object or an array. Since- the final subscript is not traversed, it may be an object key. Nested arrays- will be created and <literal>NULL</literal>-padded according to the path until- the value can be placed appropriately.+ paths as long as the last existing element to be traversed is an object or+ array, as implied by the corresponding subscript (the element indicated by+ the last subscript in the path is not traversed and may be anything). Nested+ array and object structures will be created, and in the former case+ <literal>null</literal>-padded, as specified by the subscript path until the+ assigned value can be placed.
<programlisting>
-- Where jsonb_field was {}, it is now {'a': [{'b': 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
--- Where jsonb_field was [], it is now [{'a': 1}]-UPDATE table_name SET jsonb_field[0]['a'] = '1';+-- Where jsonb_field was [], it is now [null, {'a': 1}]+UPDATE table_name SET jsonb_field[1]['a'] = '1';
</programlisting>
</para>
--
2.30.0
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: pgsql-hackers@postgresql.org
Cc: dian.m.fay@gmail.com, 9erthalion6@gmail.com, pavel.stehule@gmail.com, tgl@sss.pgh.pa.us, andres@anarazel.de, aekorotkov@gmail.com, pryzby@telsasoft.com, david@pgmasters.net, n.gluhov@postgrespro.ru, alvherre@2ndquadrant.com, david@fetter.org, thomas.munro@gmail.com, oleksandr.shulgin@zalando.de, robertmhaas@gmail.com, michael.paquier@gmail.com, obartunov@gmail.com, peter.eisentraut@2ndquadrant.com
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
In-Reply-To: <C8OK3JQNE1KN.2H0O0RCBD576E@lamia>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox