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 <[email protected]> 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
