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....@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.sgml
index 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
