From: Dian M Fay <dian.m.fay@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: Dmitry Dolgov <9erthalion6@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 09:58:43 -0500
Message-ID: <C8O2OLT387R2.1RSB78Z4ZZ335@lamia> (raw)
In-Reply-To: <CAFj8pRATxDMiwwKDZF+VyuV0QBqhLEyL5-bZrpi2cLGQKdZ1YA@mail.gmail.com>
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.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.sgmlindex 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.cindex 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.outindex 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
Attachments:
[text/plain] 0001-Revise-jsonb-subscripting-documentation.patch (9.0K, ../C8O2OLT387R2.1RSB78Z4ZZ335@lamia/2-0001-Revise-jsonb-subscripting-documentation.patch)
download | inline diff:
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.sgmlindex 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.cindex 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.outindex 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
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, pavel.stehule@gmail.com, 9erthalion6@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: <C8O2OLT387R2.1RSB78Z4ZZ335@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