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

Commit676887a

Browse files
committed
Implementation of subscripting for jsonb
Subscripting for jsonb does not support slices, does not have a limit for thenumber of subscripts, and an assignment expects a replace value to have jsonbtype. There is also one functional difference between assignment viasubscripting and assignment via jsonb_set(). When an original jsonb containeris NULL, the subscripting replaces it with an empty jsonb and proceeds withan assignment.For the sake of code reuse, we rearrange some parts of jsonb functionalityto allow the usage of the same functions for jsonb_set and assign subscriptingoperation.The original idea belongs to Oleg Bartunov.Catversion is bumped.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 parentdc43492 commit676887a

File tree

12 files changed

+988
-108
lines changed

12 files changed

+988
-108
lines changed

‎doc/src/sgml/json.sgml

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -602,6 +602,57 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
602602
</para>
603603
</sect2>
604604

605+
<sect2 id="jsonb-subscripting">
606+
<title><type>jsonb</type> Subscripting</title>
607+
<para>
608+
The <type>jsonb</type> data type supports array-style subscripting expressions
609+
to extract and modify elements. Nested values can be indicated by chaining
610+
subscripting expressions, following the same rules as the <literal>path</literal>
611+
argument in the <literal>jsonb_set</literal> function. If a <type>jsonb</type>
612+
value is an array, numeric subscripts start at zero, and negative integers count
613+
backwards from the last element of the array. Slice expressions are not supported.
614+
The result of a subscripting expression is always of the jsonb data type.
615+
</para>
616+
617+
<para>
618+
An example of subscripting syntax:
619+
<programlisting>
620+
621+
-- Extract object value by key
622+
SELECT ('{"a": 1}'::jsonb)['a'];
623+
624+
-- Extract nested object value by key path
625+
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
626+
627+
-- Extract array element by index
628+
SELECT ('[1, "2", null]'::jsonb)[1];
629+
630+
-- Update object value by key. Note the quotes around '1': the assigned
631+
-- value must be of the jsonb type as well
632+
UPDATE table_name SET jsonb_field['key'] = '1';
633+
634+
-- Filter records using a WHERE clause with subscripting. Since the result of
635+
-- subscripting is jsonb, the value we compare it against must also be jsonb.
636+
-- The double quotes make "value" also a valid jsonb string.
637+
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
638+
</programlisting>
639+
640+
<type>jsonb</type> assignment via subscripting handles a few edge cases
641+
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:
644+
645+
<programlisting>
646+
-- Where jsonb_field was NULL, it is now {"a": 1}
647+
UPDATE table_name SET jsonb_field['a'] = '1';
648+
649+
-- Where jsonb_field was NULL, it is now [1]
650+
UPDATE table_name SET jsonb_field[0] = '1';
651+
</programlisting>
652+
653+
</para>
654+
</sect2>
655+
605656
<sect2>
606657
<title>Transforms</title>
607658

‎src/backend/utils/adt/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@ OBJS = \
5050
jsonb_op.o\
5151
jsonb_util.o\
5252
jsonfuncs.o\
53+
jsonbsubs.o\
5354
jsonpath.o\
5455
jsonpath_exec.o\
5556
jsonpath_gram.o\

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

Lines changed: 62 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -68,18 +68,25 @@ static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate,
6868
JsonbIteratorTokenseq,
6969
JsonbValue*scalarVal);
7070

71+
void
72+
JsonbToJsonbValue(Jsonb*jsonb,JsonbValue*val)
73+
{
74+
val->type=jbvBinary;
75+
val->val.binary.data=&jsonb->root;
76+
val->val.binary.len=VARSIZE(jsonb)-VARHDRSZ;
77+
}
78+
7179
/*
7280
* Turn an in-memory JsonbValue into a Jsonb for on-disk storage.
7381
*
74-
* There isn't a JsonbToJsonbValue(), because generally we find it more
75-
* convenient to directly iterate through the Jsonb representation and only
76-
* really convert nested scalar values. JsonbIteratorNext() does this, so that
77-
* clients of the iteration code don't have to directly deal with the binary
78-
* representation (JsonbDeepContains() is a notable exception, although all
79-
* exceptions are internal to this module). In general, functions that accept
80-
* a JsonbValue argument are concerned with the manipulation of scalar values,
81-
* or simple containers of scalar values, where it would be inconvenient to
82-
* deal with a great amount of other state.
82+
* Generally we find it more convenient to directly iterate through the Jsonb
83+
* representation and only really convert nested scalar values.
84+
* JsonbIteratorNext() does this, so that clients of the iteration code don't
85+
* have to directly deal with the binary representation (JsonbDeepContains() is
86+
* a notable exception, although all exceptions are internal to this module).
87+
* In general, functions that accept a JsonbValue argument are concerned with
88+
* the manipulation of scalar values, or simple containers of scalar values,
89+
* where it would be inconvenient to deal with a great amount of other state.
8390
*/
8491
Jsonb*
8592
JsonbValueToJsonb(JsonbValue*val)
@@ -563,6 +570,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq,
563570
JsonbValue*res=NULL;
564571
JsonbValuev;
565572
JsonbIteratorTokentok;
573+
inti;
574+
575+
if (jbval&& (seq==WJB_ELEM||seq==WJB_VALUE)&&jbval->type==jbvObject)
576+
{
577+
pushJsonbValue(pstate,WJB_BEGIN_OBJECT,NULL);
578+
for (i=0;i<jbval->val.object.nPairs;i++)
579+
{
580+
pushJsonbValue(pstate,WJB_KEY,&jbval->val.object.pairs[i].key);
581+
pushJsonbValue(pstate,WJB_VALUE,&jbval->val.object.pairs[i].value);
582+
}
583+
584+
returnpushJsonbValue(pstate,WJB_END_OBJECT,NULL);
585+
}
586+
587+
if (jbval&& (seq==WJB_ELEM||seq==WJB_VALUE)&&jbval->type==jbvArray)
588+
{
589+
pushJsonbValue(pstate,WJB_BEGIN_ARRAY,NULL);
590+
for (i=0;i<jbval->val.array.nElems;i++)
591+
{
592+
pushJsonbValue(pstate,WJB_ELEM,&jbval->val.array.elems[i]);
593+
}
594+
595+
returnpushJsonbValue(pstate,WJB_END_ARRAY,NULL);
596+
}
566597

567598
if (!jbval|| (seq!=WJB_ELEM&&seq!=WJB_VALUE)||
568599
jbval->type!=jbvBinary)
@@ -573,9 +604,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq,
573604

574605
/* unpack the binary and add each piece to the pstate */
575606
it=JsonbIteratorInit(jbval->val.binary.data);
607+
608+
if ((jbval->val.binary.data->header&JB_FSCALAR)&&*pstate)
609+
{
610+
tok=JsonbIteratorNext(&it,&v, true);
611+
Assert(tok==WJB_BEGIN_ARRAY);
612+
Assert(v.type==jbvArray&&v.val.array.rawScalar);
613+
614+
tok=JsonbIteratorNext(&it,&v, true);
615+
Assert(tok==WJB_ELEM);
616+
617+
res=pushJsonbValueScalar(pstate,seq,&v);
618+
619+
tok=JsonbIteratorNext(&it,&v, true);
620+
Assert(tok==WJB_END_ARRAY);
621+
Assert(it==NULL);
622+
623+
returnres;
624+
}
625+
576626
while ((tok=JsonbIteratorNext(&it,&v, false))!=WJB_DONE)
577627
res=pushJsonbValueScalar(pstate,tok,
578-
tok<WJB_BEGIN_ARRAY ?&v :NULL);
628+
tok<WJB_BEGIN_ARRAY||
629+
(tok==WJB_BEGIN_ARRAY&&
630+
v.val.array.rawScalar) ?&v :NULL);
579631

580632
returnres;
581633
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp