🌐 AI搜索 & 代理 主页
Skip to content

Commit aa6e46d

Browse files
akorotkovakorotkov
committed
Throw error when assigning jsonb scalar instead of a composite object
During the jsonb subscripting assignment, the provided path might assume an object or an array where the source jsonb has a scalar value. Initial subscripting assignment logic will skip such an update operation with no message shown. This commit makes it throw an error to indicate this type of situation. Discussion: https://postgr.es/m/CA%2Bq6zcV8qvGcDXurwwgUbwACV86Th7G80pnubg42e-p9gsSf%3Dg%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcX3mdxGCgdThzuySwH-ApyHHM-G4oB1R0fn0j2hZqqkLQ%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcVDuGBv%3DM0FqBYX8DPebS3F_0KQ6OVFobGJPM507_SZ_w%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcVovR%2BXY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA%40mail.gmail.com Author: Dmitry Dolgov Reviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule, Dian M Fay Reviewed-by: Andrew Dunstan, Chapman Flack, Merlin Moncure, Peter Geoghegan Reviewed-by: Alvaro Herrera, Jim Nasby, Josh Berkus, Victor Wagner Reviewed-by: Aleksander Alekseev, Robert Haas, Oleg Bartunov
1 parent 81fcc72 commit aa6e46d

File tree

4 files changed

+104
-8
lines changed

4 files changed

+104
-8
lines changed

‎doc/src/sgml/json.sgml‎

Lines changed: 31 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -614,8 +614,24 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
614614
The result of a subscripting expression is always of the jsonb data type.
615615
</para>
616616

617+
<para>
618+
<command>UPDATE</command> statements may use subscripting in the
619+
<literal>SET</literal> clause to modify <type>jsonb</type> values. Subscript
620+
paths must be traversible for all affected values insofar as they exist. For
621+
instance, the path <literal>val['a']['b']['c']</literal> can be traversed all
622+
the way to <literal>c</literal> if every <literal>val</literal>,
623+
<literal>val['a']</literal>, and <literal>val['a']['b']</literal> is an
624+
object. If any <literal>val['a']</literal> or <literal>val['a']['b']</literal>
625+
is not defined, it will be created as an empty object and filled as
626+
necessary. However, if any <literal>val</literal> itself or one of the
627+
intermediary values is defined as a non-object such as a string, number, or
628+
<literal>jsonb</literal> <literal>null</literal>, traversal cannot proceed so
629+
an error is raised and the transaction aborted.
630+
</para>
631+
617632
<para>
618633
An example of subscripting syntax:
634+
619635
<programlisting>
620636

621637
-- Extract object value by key
@@ -631,6 +647,10 @@ SELECT ('[1, "2", null]'::jsonb)[1];
631647
-- value must be of the jsonb type as well
632648
UPDATE table_name SET jsonb_field['key'] = '1';
633649

650+
-- This will raise an error if any record's jsonb_field['a']['b'] is something
651+
-- other than an object. For example, the value {"a": 1} has no 'b' key.
652+
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';
653+
634654
-- Filter records using a WHERE clause with subscripting. Since the result of
635655
-- subscripting is jsonb, the value we compare it against must also be jsonb.
636656
-- The double quotes make "value" also a valid jsonb string.
@@ -639,8 +659,9 @@ SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
639659

640660
<type>jsonb</type> assignment via subscripting handles a few edge cases
641661
differently from <literal>jsonb_set</literal>. When a source <type>jsonb</type>
642-
is <literal>NULL</literal>, assignment via subscripting will proceed as if
643-
it was an empty JSON object:
662+
value is <literal>NULL</literal>, assignment via subscripting will proceed
663+
as if it was an empty JSON value of the type (object or array) implied by the
664+
subscript key:
644665

645666
<programlisting>
646667
-- Where jsonb_field was NULL, it is now {"a": 1}
@@ -661,17 +682,19 @@ UPDATE table_name SET jsonb_field[2] = '2';
661682
</programlisting>
662683

663684
A <type>jsonb</type> value will accept assignments to nonexistent subscript
664-
paths as long as the last existing path key is an object or an array. Since
665-
the final subscript is not traversed, it may be an object key. Nested arrays
666-
will be created and <literal>NULL</literal>-padded according to the path until
667-
the value can be placed appropriately.
685+
paths as long as the last existing element to be traversed is an object or
686+
array, as implied by the corresponding subscript (the element indicated by
687+
the last subscript in the path is not traversed and may be anything). Nested
688+
array and object structures will be created, and in the former case
689+
<literal>null</literal>-padded, as specified by the subscript path until the
690+
assigned value can be placed.
668691

669692
<programlisting>
670693
-- Where jsonb_field was {}, it is now {'a': [{'b': 1}]}
671694
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
672695

673-
-- Where jsonb_field was [], it is now [{'a': 1}]
674-
UPDATE table_name SET jsonb_field[0]['a'] = '1';
696+
-- Where jsonb_field was [], it is now [null, {'a': 1}]
697+
UPDATE table_name SET jsonb_field[1]['a'] = '1';
675698
</programlisting>
676699

677700
</para>

‎src/backend/utils/adt/jsonfuncs.c‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4931,6 +4931,21 @@ setPath(JsonbIterator **it, Datum *path_elems,
49314931
switch (r)
49324932
{
49334933
case WJB_BEGIN_ARRAY:
4934+
4935+
/*
4936+
* If instructed complain about attempts to replace whithin a raw
4937+
* scalar value. This happens even when current level is equal to
4938+
* path_len, because the last path key should also correspond to
4939+
* an object or an array, not raw scalar.
4940+
*/
4941+
if ((op_type & JB_PATH_FILL_GAPS) && (level <= path_len - 1) &&
4942+
v.val.array.rawScalar)
4943+
ereport(ERROR,
4944+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4945+
errmsg("cannot replace existing key"),
4946+
errdetail("The path assumes key is a composite object, "
4947+
"but it is a scalar value.")));
4948+
49344949
(void) pushJsonbValue(st, r, NULL);
49354950
setPathArray(it, path_elems, path_nulls, path_len, st, level,
49364951
newval, v.val.array.nElems, op_type);
@@ -4948,6 +4963,20 @@ setPath(JsonbIterator **it, Datum *path_elems,
49484963
break;
49494964
case WJB_ELEM:
49504965
case WJB_VALUE:
4966+
4967+
/*
4968+
* If instructed complain about attempts to replace whithin a
4969+
* scalar value. This happens even when current level is equal to
4970+
* path_len, because the last path key should also correspond to
4971+
* an object or an array, not an element or value.
4972+
*/
4973+
if ((op_type & JB_PATH_FILL_GAPS) && (level <= path_len - 1))
4974+
ereport(ERROR,
4975+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4976+
errmsg("cannot replace existing key"),
4977+
errdetail("The path assumes key is a composite object, "
4978+
"but it is a scalar value.")));
4979+
49514980
res = pushJsonbValue(st, r, &v);
49524981
break;
49534982
default:

‎src/test/regress/expected/jsonb.out‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5134,6 +5134,33 @@ select * from test_jsonb_subscript;
51345134
1 | {"a": [null, {"c": [null, null, 1]}]}
51355135
(1 row)
51365136

5137+
-- trying replace assuming a composite object, but it's an element or a value
5138+
delete from test_jsonb_subscript;
5139+
insert into test_jsonb_subscript values (1, '{"a": 1}');
5140+
update test_jsonb_subscript set test_json['a']['b'] = '1';
5141+
ERROR: cannot replace existing key
5142+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5143+
update test_jsonb_subscript set test_json['a']['b']['c'] = '1';
5144+
ERROR: cannot replace existing key
5145+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5146+
update test_jsonb_subscript set test_json['a'][0] = '1';
5147+
ERROR: cannot replace existing key
5148+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5149+
update test_jsonb_subscript set test_json['a'][0]['c'] = '1';
5150+
ERROR: cannot replace existing key
5151+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5152+
update test_jsonb_subscript set test_json['a'][0][0] = '1';
5153+
ERROR: cannot replace existing key
5154+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5155+
-- trying replace assuming a composite object, but it's a raw scalar
5156+
delete from test_jsonb_subscript;
5157+
insert into test_jsonb_subscript values (1, 'null');
5158+
update test_jsonb_subscript set test_json[0] = '1';
5159+
ERROR: cannot replace existing key
5160+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
5161+
update test_jsonb_subscript set test_json[0][0] = '1';
5162+
ERROR: cannot replace existing key
5163+
DETAIL: The path assumes key is a composite object, but it is a scalar value.
51375164
-- jsonb to tsvector
51385165
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb);
51395166
to_tsvector

‎src/test/regress/sql/jsonb.sql‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1371,6 +1371,23 @@ insert into test_jsonb_subscript values (1, '{"a": []}');
13711371
update test_jsonb_subscript set test_json['a'][1]['c'][2] = '1';
13721372
select * from test_jsonb_subscript;
13731373

1374+
-- trying replace assuming a composite object, but it's an element or a value
1375+
1376+
delete from test_jsonb_subscript;
1377+
insert into test_jsonb_subscript values (1, '{"a": 1}');
1378+
update test_jsonb_subscript set test_json['a']['b'] = '1';
1379+
update test_jsonb_subscript set test_json['a']['b']['c'] = '1';
1380+
update test_jsonb_subscript set test_json['a'][0] = '1';
1381+
update test_jsonb_subscript set test_json['a'][0]['c'] = '1';
1382+
update test_jsonb_subscript set test_json['a'][0][0] = '1';
1383+
1384+
-- trying replace assuming a composite object, but it's a raw scalar
1385+
1386+
delete from test_jsonb_subscript;
1387+
insert into test_jsonb_subscript values (1, 'null');
1388+
update test_jsonb_subscript set test_json[0] = '1';
1389+
update test_jsonb_subscript set test_json[0][0] = '1';
1390+
13741391
-- jsonb to tsvector
13751392
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb);
13761393

0 commit comments

Comments
 (0)