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

Commit0b62fd0

Browse files
committed
Add jsonb_insert
It inserts a new value into an jsonb array at arbitrary position ora new key to jsonb object.Author: Dmitry DolgovReviewers: Petr Jelinek, Vitaly Burovoy, Andrew Dunstan
1 parent3b3fcc4 commit0b62fd0

File tree

8 files changed

+324
-29
lines changed

8 files changed

+324
-29
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 41 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10902,6 +10902,9 @@ table2-mapping
1090210902
<indexterm>
1090310903
<primary>jsonb_set</primary>
1090410904
</indexterm>
10905+
<indexterm>
10906+
<primary>jsonb_insert</primary>
10907+
</indexterm>
1090510908
<indexterm>
1090610909
<primary>jsonb_pretty</primary>
1090710910
</indexterm>
@@ -11183,6 +11186,39 @@ table2-mapping
1118311186
</para><para><literal>[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</literal>
1118411187
</para></entry>
1118511188
</row>
11189+
<row>
11190+
<entry>
11191+
<para><literal>
11192+
jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_after</parameter> <type>boolean</type></optional>)
11193+
</literal></para>
11194+
</entry>
11195+
<entry><para><type>jsonb</type></para></entry>
11196+
<entry>
11197+
Returns <replaceable>target</replaceable> with
11198+
<replaceable>new_value</replaceable> inserted. If
11199+
<replaceable>target</replaceable> section designated by
11200+
<replaceable>path</replaceable> is in a JSONB array,
11201+
<replaceable>new_value</replaceable> will be inserted before target or
11202+
after if <replaceable>insert_after</replaceable> is true (default is
11203+
<literal>false</>). If <replaceable>target</replaceable> section
11204+
designated by <replaceable>path</replaceable> is in JSONB object,
11205+
<replaceable>new_value</replaceable> will be inserted only if
11206+
<replaceable>target</replaceable> does not exist. As with the path
11207+
orientated operators, negative integers that appear in
11208+
<replaceable>path</replaceable> count from the end of JSON arrays.
11209+
</entry>
11210+
<entry>
11211+
<para><literal>
11212+
jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
11213+
</literal></para>
11214+
<para><literal>
11215+
jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
11216+
</literal></para>
11217+
</entry>
11218+
<entry><para><literal>{"a": [0, "new_value", 1, 2]}</literal>
11219+
</para><para><literal>{"a": [0, 1, "new_value", 2]}</literal>
11220+
</para></entry>
11221+
</row>
1118611222
<row>
1118711223
<entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
1118811224
</para></entry>
@@ -11235,10 +11271,11 @@ table2-mapping
1123511271
<note>
1123611272
<para>
1123711273
All the items of the <literal>path</> parameter of <literal>jsonb_set</>
11238-
must be present in the <literal>target</>, unless
11239-
<literal>create_missing</> is true, in which case all but the last item
11240-
must be present. If these conditions are not met the <literal>target</>
11241-
is returned unchanged.
11274+
as well as <literal>jsonb_insert</> except the last item must be present
11275+
in the <literal>target</>. If <literal>create_missing</> is false, all
11276+
items of the <literal>path</> parameter of <literal>jsonb_set</> must be
11277+
present. If these conditions are not met the <literal>target</> is
11278+
returned unchanged.
1124211279
</para>
1124311280
<para>
1124411281
If the last path item is an object key, it will be created if it

‎src/backend/catalog/system_views.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -997,3 +997,11 @@ RETURNS text[]
997997
LANGUAGE INTERNAL
998998
STRICT IMMUTABLE
999999
AS'parse_ident';
1000+
1001+
CREATEOR REPLACE FUNCTION
1002+
jsonb_insert(jsonb_in jsonb,pathtext[] , replacement jsonb,
1003+
insert_afterboolean DEFAULT false)
1004+
RETURNS jsonb
1005+
LANGUAGE INTERNAL
1006+
STRICT IMMUTABLE
1007+
AS'jsonb_insert';

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

Lines changed: 110 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,15 @@
3333
#include"utils/memutils.h"
3434
#include"utils/typcache.h"
3535

36+
/* Operations available for setPath */
37+
#defineJB_PATH_NOOP0x0000
38+
#defineJB_PATH_CREATE0x0001
39+
#defineJB_PATH_DELETE0x0002
40+
#defineJB_PATH_INSERT_BEFORE0x0004
41+
#defineJB_PATH_INSERT_AFTER0x0008
42+
#defineJB_PATH_CREATE_OR_INSERT \
43+
(JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE)
44+
3645
/* semantic action functions for json_object_keys */
3746
staticvoidokeys_object_field_start(void*state,char*fname,boolisnull);
3847
staticvoidokeys_array_start(void*state);
@@ -130,14 +139,14 @@ static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
130139
staticJsonbValue*setPath(JsonbIterator**it,Datum*path_elems,
131140
bool*path_nulls,intpath_len,
132141
JsonbParseState**st,intlevel,Jsonb*newval,
133-
boolcreate);
142+
intop_type);
134143
staticvoidsetPathObject(JsonbIterator**it,Datum*path_elems,
135144
bool*path_nulls,intpath_len,JsonbParseState**st,
136145
intlevel,
137-
Jsonb*newval,uint32npairs,boolcreate);
146+
Jsonb*newval,uint32npairs,intop_type);
138147
staticvoidsetPathArray(JsonbIterator**it,Datum*path_elems,
139148
bool*path_nulls,intpath_len,JsonbParseState**st,
140-
intlevel,Jsonb*newval,uint32nelems,boolcreate);
149+
intlevel,Jsonb*newval,uint32nelems,intop_type);
141150
staticvoidaddJsonbToParseState(JsonbParseState**jbps,Jsonb*jb);
142151

143152
/* state for json_object_keys */
@@ -3544,7 +3553,7 @@ jsonb_set(PG_FUNCTION_ARGS)
35443553
it=JsonbIteratorInit(&in->root);
35453554

35463555
res=setPath(&it,path_elems,path_nulls,path_len,&st,
3547-
0,newval,create);
3556+
0,newval,create ?JB_PATH_CREATE :JB_PATH_NOOP);
35483557

35493558
Assert(res!=NULL);
35503559

@@ -3588,7 +3597,52 @@ jsonb_delete_path(PG_FUNCTION_ARGS)
35883597

35893598
it=JsonbIteratorInit(&in->root);
35903599

3591-
res=setPath(&it,path_elems,path_nulls,path_len,&st,0,NULL, false);
3600+
res=setPath(&it,path_elems,path_nulls,path_len,&st,
3601+
0,NULL,JB_PATH_DELETE);
3602+
3603+
Assert(res!=NULL);
3604+
3605+
PG_RETURN_JSONB(JsonbValueToJsonb(res));
3606+
}
3607+
3608+
/*
3609+
* SQL function jsonb_insert(jsonb, text[], jsonb, boolean)
3610+
*
3611+
*/
3612+
Datum
3613+
jsonb_insert(PG_FUNCTION_ARGS)
3614+
{
3615+
Jsonb*in=PG_GETARG_JSONB(0);
3616+
ArrayType*path=PG_GETARG_ARRAYTYPE_P(1);
3617+
Jsonb*newval=PG_GETARG_JSONB(2);
3618+
boolafter=PG_GETARG_BOOL(3);
3619+
JsonbValue*res=NULL;
3620+
Datum*path_elems;
3621+
bool*path_nulls;
3622+
intpath_len;
3623+
JsonbIterator*it;
3624+
JsonbParseState*st=NULL;
3625+
3626+
if (ARR_NDIM(path)>1)
3627+
ereport(ERROR,
3628+
(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
3629+
errmsg("wrong number of array subscripts")));
3630+
3631+
if (JB_ROOT_IS_SCALAR(in))
3632+
ereport(ERROR,
3633+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3634+
errmsg("cannot set path in scalar")));
3635+
3636+
deconstruct_array(path,TEXTOID,-1, false,'i',
3637+
&path_elems,&path_nulls,&path_len);
3638+
3639+
if (path_len==0)
3640+
PG_RETURN_JSONB(in);
3641+
3642+
it=JsonbIteratorInit(&in->root);
3643+
3644+
res=setPath(&it,path_elems,path_nulls,path_len,&st,0,newval,
3645+
after ?JB_PATH_INSERT_AFTER :JB_PATH_INSERT_BEFORE);
35923646

35933647
Assert(res!=NULL);
35943648

@@ -3707,18 +3761,23 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
37073761
}
37083762

37093763
/*
3710-
* Do most of the heavy work for jsonb_set
3764+
* Do most of the heavy work for jsonb_set/jsonb_insert
3765+
*
3766+
* If JB_PATH_DELETE bit is set in op_type, the element is to be removed.
37113767
*
3712-
* If newval is null, the element is to be removed.
3768+
* If any bit mentioned in JB_PATH_CREATE_OR_INSERT is set in op_type,
3769+
* we create the new value if the key or array index does not exist.
37133770
*
3714-
* If create is true, we create the new value if the key or array index
3715-
* does not exist. All path elements before the last must already exist
3716-
* whether or not create is true, or nothing is done.
3771+
* Bits JB_PATH_INSERT_BEFORE and JB_PATH_INSERT_AFTER in op_type
3772+
* behave as JB_PATH_CREATE if new value is inserted in JsonbObject.
3773+
*
3774+
* All path elements before the last must already exist
3775+
* whatever bits in op_type are set, or nothing is done.
37173776
*/
37183777
staticJsonbValue*
37193778
setPath(JsonbIterator**it,Datum*path_elems,
37203779
bool*path_nulls,intpath_len,
3721-
JsonbParseState**st,intlevel,Jsonb*newval,boolcreate)
3780+
JsonbParseState**st,intlevel,Jsonb*newval,intop_type)
37223781
{
37233782
JsonbValuev;
37243783
JsonbIteratorTokenr;
@@ -3739,15 +3798,15 @@ setPath(JsonbIterator **it, Datum *path_elems,
37393798
caseWJB_BEGIN_ARRAY:
37403799
(void)pushJsonbValue(st,r,NULL);
37413800
setPathArray(it,path_elems,path_nulls,path_len,st,level,
3742-
newval,v.val.array.nElems,create);
3801+
newval,v.val.array.nElems,op_type);
37433802
r=JsonbIteratorNext(it,&v, false);
37443803
Assert(r==WJB_END_ARRAY);
37453804
res=pushJsonbValue(st,r,NULL);
37463805
break;
37473806
caseWJB_BEGIN_OBJECT:
37483807
(void)pushJsonbValue(st,r,NULL);
37493808
setPathObject(it,path_elems,path_nulls,path_len,st,level,
3750-
newval,v.val.object.nPairs,create);
3809+
newval,v.val.object.nPairs,op_type);
37513810
r=JsonbIteratorNext(it,&v, true);
37523811
Assert(r==WJB_END_OBJECT);
37533812
res=pushJsonbValue(st,r,NULL);
@@ -3771,7 +3830,7 @@ setPath(JsonbIterator **it, Datum *path_elems,
37713830
staticvoid
37723831
setPathObject(JsonbIterator**it,Datum*path_elems,bool*path_nulls,
37733832
intpath_len,JsonbParseState**st,intlevel,
3774-
Jsonb*newval,uint32npairs,boolcreate)
3833+
Jsonb*newval,uint32npairs,intop_type)
37753834
{
37763835
JsonbValuev;
37773836
inti;
@@ -3782,7 +3841,8 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
37823841
done= true;
37833842

37843843
/* empty object is a special case for create */
3785-
if ((npairs==0)&&create&& (level==path_len-1))
3844+
if ((npairs==0)&& (op_type&JB_PATH_CREATE_OR_INSERT)&&
3845+
(level==path_len-1))
37863846
{
37873847
JsonbValuenewkey;
37883848

@@ -3807,8 +3867,19 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
38073867
{
38083868
if (level==path_len-1)
38093869
{
3810-
r=JsonbIteratorNext(it,&v, true);/* skip */
3811-
if (newval!=NULL)
3870+
/*
3871+
* called from jsonb_insert(), it forbids redefining
3872+
* an existsing value
3873+
*/
3874+
if (op_type& (JB_PATH_INSERT_BEFORE |JB_PATH_INSERT_AFTER))
3875+
ereport(ERROR,
3876+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3877+
errmsg("cannot replace existing key"),
3878+
errhint("Try using the function jsonb_set "
3879+
"to replace key value.")));
3880+
3881+
r=JsonbIteratorNext(it,&v, true);/* skip value */
3882+
if (!(op_type&JB_PATH_DELETE))
38123883
{
38133884
(void)pushJsonbValue(st,WJB_KEY,&k);
38143885
addJsonbToParseState(st,newval);
@@ -3819,12 +3890,13 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
38193890
{
38203891
(void)pushJsonbValue(st,r,&k);
38213892
setPath(it,path_elems,path_nulls,path_len,
3822-
st,level+1,newval,create);
3893+
st,level+1,newval,op_type);
38233894
}
38243895
}
38253896
else
38263897
{
3827-
if (create&& !done&&level==path_len-1&&i==npairs-1)
3898+
if ((op_type&JB_PATH_CREATE_OR_INSERT)&& !done&&
3899+
level==path_len-1&&i==npairs-1)
38283900
{
38293901
JsonbValuenewkey;
38303902

@@ -3865,7 +3937,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
38653937
staticvoid
38663938
setPathArray(JsonbIterator**it,Datum*path_elems,bool*path_nulls,
38673939
intpath_len,JsonbParseState**st,intlevel,
3868-
Jsonb*newval,uint32nelems,boolcreate)
3940+
Jsonb*newval,uint32nelems,intop_type)
38693941
{
38703942
JsonbValuev;
38713943
intidx,
@@ -3909,7 +3981,8 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
39093981
* what the idx value is
39103982
*/
39113983

3912-
if ((idx==INT_MIN||nelems==0)&&create&& (level==path_len-1))
3984+
if ((idx==INT_MIN||nelems==0)&& (level==path_len-1)&&
3985+
(op_type&JB_PATH_CREATE_OR_INSERT))
39133986
{
39143987
Assert(newval!=NULL);
39153988
addJsonbToParseState(st,newval);
@@ -3926,14 +3999,26 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
39263999
if (level==path_len-1)
39274000
{
39284001
r=JsonbIteratorNext(it,&v, true);/* skip */
3929-
if (newval!=NULL)
4002+
4003+
if (op_type& (JB_PATH_INSERT_BEFORE |JB_PATH_CREATE))
4004+
addJsonbToParseState(st,newval);
4005+
4006+
/*
4007+
* We should keep current value only in case of
4008+
* JB_PATH_INSERT_BEFORE or JB_PATH_INSERT_AFTER
4009+
* because otherwise it should be deleted or replaced
4010+
*/
4011+
if (op_type& (JB_PATH_INSERT_AFTER |JB_PATH_INSERT_BEFORE))
4012+
(void)pushJsonbValue(st,r,&v);
4013+
4014+
if (op_type&JB_PATH_INSERT_AFTER)
39304015
addJsonbToParseState(st,newval);
39314016

39324017
done= true;
39334018
}
39344019
else
39354020
(void)setPath(it,path_elems,path_nulls,path_len,
3936-
st,level+1,newval,create);
4021+
st,level+1,newval,op_type);
39374022
}
39384023
else
39394024
{
@@ -3958,7 +4043,8 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
39584043
}
39594044
}
39604045

3961-
if (create&& !done&&level==path_len-1&&i==nelems-1)
4046+
if (op_type&JB_PATH_CREATE_OR_INSERT&& !done&&
4047+
level==path_len-1&&i==nelems-1)
39624048
{
39634049
addJsonbToParseState(st,newval);
39644050
}

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201604053
56+
#defineCATALOG_VERSION_NO201604061
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4869,6 +4869,8 @@ DATA(insert OID = 3305 ( jsonb_set PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4
48694869
DESCR("Set part of a jsonb");
48704870
DATA(insert OID = 3306 ( jsonb_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _null_ _null_ _null_ ));
48714871
DESCR("Indented text from jsonb");
4872+
DATA(insert OID = 3579 ( jsonb_insert PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4 0 3802 "3802 1009 3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_insert _null_ _null_ _null_ ));
4873+
DESCR("Insert value into a jsonb");
48724874
/* txid */
48734875
DATA(insert OID = 2939 ( txid_snapshot_inPGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2970 "2275" _null_ _null_ _null_ _null_ _null_ txid_snapshot_in _null_ _null_ _null_ ));
48744876
DESCR("I/O");

‎src/include/utils/jsonb.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -408,6 +408,9 @@ extern Datum jsonb_delete_path(PG_FUNCTION_ARGS);
408408
/* replacement */
409409
externDatumjsonb_set(PG_FUNCTION_ARGS);
410410

411+
/* insert after or before (for arrays) */
412+
externDatumjsonb_insert(PG_FUNCTION_ARGS);
413+
411414
/* Support functions */
412415
externuint32getJsonbOffset(constJsonbContainer*jc,intindex);
413416
externuint32getJsonbLength(constJsonbContainer*jc,intindex);

0 commit comments

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