On Tue Jan 19, 2021 at 1:42 PM EST, Pavel Stehule wrote:
> Hi
>
> I found minor issues.
>
> Doc - missing tag
>
> and three whitespaces issues
>
> see attached patch
>
> Following sentence is hard to read due long nested example
>
> If the
> + path contradicts structure of modified <type>jsonb</type> for any
> individual
> + value (e.g. path <literal>val['a']['b']['c']</literal> assumes keys
> + <literal>'a'</literal> and <literal>'b'</literal> have object values
> + assigned to them, but if <literal>val['a']</literal> or
> + <literal>val['b']</literal> is null, a string, or a number, then the
> path
> + contradicts with the existing structure), an error is raised even if
> other
> + values do conform.
>
> It can be divided into two sentences - predicate, and example.
>
> Regards
>
> Pavel
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.
From 086a34ca860e8513484d829db1cb3f0c17c4ec1e Mon Sep 17 00:00:00 2001
From: Dian M Fay <dian.m....@gmail.com>
Date: Tue, 19 Jan 2021 23:44:23 -0500
Subject: [PATCH] Revise jsonb subscripting documentation
---
doc/src/sgml/json.sgml | 101 +++++++++++++---------------
src/backend/utils/adt/jsonbsubs.c | 2 +-
src/test/regress/expected/jsonb.out | 2 +-
3 files changed, 49 insertions(+), 56 deletions(-)
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 4e19fe4fb8..eb3952193a 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -605,97 +605,90 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE
jdoc @> '{"tags": ["qu
<sect2 id="jsonb-subscripting">
<title><type>jsonb</type> Subscripting</title>
<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 jsonb data type.
+ The <type>jsonb</type> data type supports array-style subscripting
expressions
+ to extract and modify elements. Nested values can be indicated by chaining
+ subscripting expressions, following the same rules as the
<literal>path</literal>
+ argument in the <literal>jsonb_set</literal> function. If a
<type>jsonb</type>
+ value is an array, numeric subscripts start at zero, and negative integers
count
+ backwards from the last element of the array. Slice expressions are not
supported.
+ The result of a subscripting expression 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 contradicts structure of modified <type>jsonb</type> for any individual
- value (e.g. path <literal>val['a']['b']['c']</literal> assumes keys
- <literal>'a'</literal> and <literal>'b'</literal> have object values
- assigned to them, but if <literal>val['a']</literal> or
- <literal>val['b']</literal> is null, a string, or a number, then the path
- contradicts with the existing structure), an error is raised even if other
- values do conform.
+ <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.
</para>
- <para>
+ <para>
An example of subscripting syntax:
+
<programlisting>
--- Extract value by key
+-- Extract object value by key
SELECT ('{"a": 1}'::jsonb)['a'];
--- Extract nested value by key path
+-- Extract nested object value by key path
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
--- Extract element by index
+-- Extract array element by index
SELECT ('[1, "2", null]'::jsonb)[1];
--- Update value by key, note the single quotes - the assigned value
--- needs to be of jsonb type as well
+-- Update object value by key. Note the quotes around '1': the assigned
+-- value must be of the jsonb type as well
UPDATE table_name SET jsonb_field['key'] = '1';
--- This will raise an error if jsonb_field is {"a": 1}
+-- This will raise an error if any record's jsonb_field['a']['b'] is something
+-- other than an object. For example, the value {"a": 1} has no 'b' key.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';
--- Select records using where clause with subscripting. Since the result of
--- subscripting is jsonb and we basically want to compare two jsonb objects, we
--- need to put the value in double quotes to be able to convert it to jsonb.
+-- Filter records using a WHERE clause with subscripting. Since the result of
+-- subscripting is jsonb, the value we compare it against must also be jsonb.
+-- The double quotes make "value" also a valid jsonb string.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
</programlisting>
- Subscripting for <type>jsonb</type> does not support slice expressions,
- even if it contains an array.
+ <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:
- In case if source <type>jsonb</type> is <literal>NULL</literal>, assignment
- via subscripting will proceed as if it was an empty JSON object:
<programlisting>
--- If jsonb_field here is NULL, the result is {"a": 1}
+-- Where jsonb_field was NULL, it is now {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';
--- If jsonb_field here is NULL, the result is [1]
+-- Where jsonb_field was NULL, it is now [1]
UPDATE table_name SET jsonb_field[0] = '1';
</programlisting>
- Jsonb assignment via subscripting handles few edge cases differently
- from <literal>jsonb_set</literal>. When assigning to the jsonb array
- to the specified index, but there are no other elements present, the
- result will be a jsonb array with the ewn value by specified index and
- <type>null</type> elements from the first index to the specified index.
-
-<programlisting>
--- If jsonb_field is [], the result is [null, null, 2]
-UPDATE table_name SET jsonb_field[2] = '2';
-</programlisting>
-
- When assigning to the jsonb array to the specified index, but position
- of the last element in the array is less than the specified index, the
- result will be a jsonb array with the new value by specified index and
- <type>null</type> elements from the last index to the specified index.
+ If an index is specified for an array containing too few elements,
+ <literal>NULL</literal> elements will be appended until the index is
reachable
+ and the value can be set.
<programlisting>
--- If jsonb_field is [0], the result is [0, null, 2]
+-- Where jsonb_field was [], it is now [null, null, 2];
+-- where jsonb_field was [0], it is now [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';
</programlisting>
- When assigning using the path which is not present in the source jsonb,
- the result will be a jsonb with the specified path created and the new
- value at the end of the path.
+ A <type>jsonb</type> value will accept assignments to nonexistent subscript
+ paths as long as the nonexistent elements being traversed are all arrays.
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.
<programlisting>
--- If jsonb_field is {}, the result is {'a': [{'b': 1}]}
+-- Where jsonb_field was {}, it is now {'a': [{'b': 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
--- If jsonb_field is [], the result is [{'a': 1}]
+-- Where jsonb_field was [], it is now [{'a': 1}]
UPDATE table_name SET jsonb_field[0]['a'] = '1';
</programlisting>
diff --git a/src/backend/utils/adt/jsonbsubs.c
b/src/backend/utils/adt/jsonbsubs.c
index 64979f3a5b..5237414be4 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -116,7 +116,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("subscript type
is not supported"),
- errhint("Jsonb
subscript must be coercet to either integer or text"),
+ errhint("Jsonb
subscript must be coerced to either integer or text"),
parser_errposition(pstate, exprLocation(subExpr))));
}
else
diff --git a/src/test/regress/expected/jsonb.out
b/src/test/regress/expected/jsonb.out
index 0df808c36d..1d33457788 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4794,7 +4794,7 @@ select ('[1, "2", null]'::jsonb)[1.0];
ERROR: subscript type is not supported
LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
^
-HINT: Jsonb subscript must be coercet to either integer or text
+HINT: Jsonb subscript must be coerced to either integer or text
select ('[1, "2", null]'::jsonb)[2];
jsonb
-------
--
2.30.0
