Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitaa6e46d

Browse files
committed
Throw error when assigning jsonb scalar instead of a composite object
During the jsonb subscripting assignment, the provided path might assume anobject or an array where the source jsonb has a scalar value. Initialsubscripting assignment logic will skip such an update operation with nomessage shown. This commit makes it throw an error to indicate this typeof situation.Discussion:https://postgr.es/m/CA%2Bq6zcV8qvGcDXurwwgUbwACV86Th7G80pnubg42e-p9gsSf%3Dg%40mail.gmail.comDiscussion:https://postgr.es/m/CA%2Bq6zcX3mdxGCgdThzuySwH-ApyHHM-G4oB1R0fn0j2hZqqkLQ%40mail.gmail.comDiscussion:https://postgr.es/m/CA%2Bq6zcVDuGBv%3DM0FqBYX8DPebS3F_0KQ6OVFobGJPM507_SZ_w%40mail.gmail.comDiscussion:https://postgr.es/m/CA%2Bq6zcVovR%2BXY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA%40mail.gmail.comAuthor: Dmitry DolgovReviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule, Dian M FayReviewed-by: Andrew Dunstan, Chapman Flack, Merlin Moncure, Peter GeogheganReviewed-by: Alvaro Herrera, Jim Nasby, Josh Berkus, Victor WagnerReviewed-by: Aleksander Alekseev, Robert Haas, Oleg Bartunov
1 parent81fcc72 commitaa6e46d

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
caseWJB_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
caseWJB_ELEM:
49504965
caseWJB_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_subscriptset 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+
deletefrom test_jsonb_subscript;
1377+
insert into test_jsonb_subscriptvalues (1,'{"a": 1}');
1378+
update test_jsonb_subscriptset test_json['a']['b']='1';
1379+
update test_jsonb_subscriptset test_json['a']['b']['c']='1';
1380+
update test_jsonb_subscriptset test_json['a'][0]='1';
1381+
update test_jsonb_subscriptset test_json['a'][0]['c']='1';
1382+
update test_jsonb_subscriptset test_json['a'][0][0]='1';
1383+
1384+
-- trying replace assuming a composite object, but it's a raw scalar
1385+
1386+
deletefrom test_jsonb_subscript;
1387+
insert into test_jsonb_subscriptvalues (1,'null');
1388+
update test_jsonb_subscriptset test_json[0]='1';
1389+
update test_jsonb_subscriptset 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)
[8]ページ先頭
©2009-2025 Movatter.jp