0001-Revise-jsonb-subscripting-documentation.patch

From 086a34ca860e8513484d829db1cb3f0c17c4ec1e Mon Sep 17 00:00:00 2001
From: Dian M Fay <dian.m.fay@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-&gt;'guid', jdoc-&gt;'name' FROM api WHERE jdoc @&gt; '{"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