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

Commitd00ca33

Browse files
committed
Implement array version of jsonb_delete and operator
This makes it possible to delete multiple keys from a jsonb value bypassing in an array of text values, which makes the operaiton muchfaster than individually deleting the keys (which would require copyingthe jsonb structure over and over again.Reviewed by Dmitry Dolgov and Michael Paquier
1 parentc22ecc6 commitd00ca33

File tree

6 files changed

+120
-1
lines changed

6 files changed

+120
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10843,6 +10843,14 @@ table2-mapping
1084310843
on their key value.</entry>
1084410844
<entry><literal>'{"a": "b"}'::jsonb - 'a' </literal></entry>
1084510845
</row>
10846+
<row>
10847+
<entry><literal>-</literal></entry>
10848+
<entry><type>text[]</type></entry>
10849+
<entry>Delete multiple key/value pairs or <emphasis>string</emphasis>
10850+
elements from left operand. Key/value pairs are matched based
10851+
on their key value.</entry>
10852+
<entry><literal>'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] </literal></entry>
10853+
</row>
1084610854
<row>
1084710855
<entry><literal>-</literal></entry>
1084810856
<entry><type>integer</type></entry>

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

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3437,6 +3437,92 @@ jsonb_delete(PG_FUNCTION_ARGS)
34373437
PG_RETURN_JSONB(JsonbValueToJsonb(res));
34383438
}
34393439

3440+
/*
3441+
* SQL function jsonb_delete (jsonb, variadic text[])
3442+
*
3443+
* return a copy of the jsonb with the indicated items
3444+
* removed.
3445+
*/
3446+
Datum
3447+
jsonb_delete_array(PG_FUNCTION_ARGS)
3448+
{
3449+
Jsonb*in=PG_GETARG_JSONB(0);
3450+
ArrayType*keys=PG_GETARG_ARRAYTYPE_P(1);
3451+
Datum*keys_elems;
3452+
bool*keys_nulls;
3453+
intkeys_len;
3454+
JsonbParseState*state=NULL;
3455+
JsonbIterator*it;
3456+
JsonbValuev,
3457+
*res=NULL;
3458+
boolskipNested= false;
3459+
JsonbIteratorTokenr;
3460+
3461+
if (ARR_NDIM(keys)>1)
3462+
ereport(ERROR,
3463+
(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
3464+
errmsg("wrong number of array subscripts")));
3465+
3466+
if (JB_ROOT_IS_SCALAR(in))
3467+
ereport(ERROR,
3468+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3469+
errmsg("cannot delete from scalar")));
3470+
3471+
if (JB_ROOT_COUNT(in)==0)
3472+
PG_RETURN_JSONB(in);
3473+
3474+
deconstruct_array(keys,TEXTOID,-1, false,'i',
3475+
&keys_elems,&keys_nulls,&keys_len);
3476+
3477+
if (keys_len==0)
3478+
PG_RETURN_JSONB(in);
3479+
3480+
it=JsonbIteratorInit(&in->root);
3481+
3482+
while ((r=JsonbIteratorNext(&it,&v,skipNested))!=0)
3483+
{
3484+
skipNested= true;
3485+
3486+
if ((r==WJB_ELEM||r==WJB_KEY)&&v.type==jbvString)
3487+
{
3488+
inti;
3489+
boolfound= false;
3490+
3491+
for (i=0;i<keys_len;i++)
3492+
{
3493+
char*keyptr;
3494+
intkeylen;
3495+
3496+
if (keys_nulls[i])
3497+
continue;
3498+
3499+
keyptr=VARDATA_ANY(keys_elems[i]);
3500+
keylen=VARSIZE_ANY_EXHDR(keys_elems[i]);
3501+
if (keylen==v.val.string.len&&
3502+
memcmp(keyptr,v.val.string.val,keylen)==0)
3503+
{
3504+
found= true;
3505+
break;
3506+
}
3507+
}
3508+
if (found)
3509+
{
3510+
/* skip corresponding value as well */
3511+
if (r==WJB_KEY)
3512+
JsonbIteratorNext(&it,&v, true);
3513+
3514+
continue;
3515+
}
3516+
}
3517+
3518+
res=pushJsonbValue(&state,r,r<WJB_BEGIN_ARRAY ?&v :NULL);
3519+
}
3520+
3521+
Assert(res!=NULL);
3522+
3523+
PG_RETURN_JSONB(JsonbValueToJsonb(res));
3524+
}
3525+
34403526
/*
34413527
* SQL function jsonb_delete (jsonb, int)
34423528
*

‎src/include/catalog/pg_operator.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1826,6 +1826,8 @@ DATA(insert OID = 3284 ( "||" PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_con
18261826
DESCR("concatenate");
18271827
DATA(insertOID=3285 ("-"PGNSPPGUIDbff3802253802003302-- ));
18281828
DESCR("delete object field");
1829+
DATA(insertOID=3398 ("-"PGNSPPGUIDbff380210093802003343--));
1830+
DESCR("delete object fields");
18291831
DATA(insertOID=3286 ("-"PGNSPPGUIDbff3802233802003303-- ));
18301832
DESCR("delete array element");
18311833
DATA(insertOID=3287 ("#-"PGNSPPGUIDbff38021009380200jsonb_delete_path-- ));

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -957,7 +957,7 @@ DESCR("name of the current database");
957957
DATA(insert OID = 817 ( current_query PGNSP PGUID 12 1 0 0 0 f f f f f f v r 0 0 25 "" _null_ _null_ _null_ _null__null_ current_query _null_ _null_ _null_ ));
958958
DESCR("get the currently executing query");
959959

960-
DATA(insert OID =3343 ( int8_mul_cash PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "20 790" _null_ _null_ _null_ _null_ _null_ int8_mul_cash _null_ _null_ _null_ ));
960+
DATA(insert OID =3399 ( int8_mul_cash PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "20 790" _null_ _null_ _null_ _null_ _null_ int8_mul_cash _null_ _null_ _null_ ));
961961
DATA(insert OID = 862 ( int4_mul_cash PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "23 790" _null_ _null_ _null_ _null_ _null_ int4_mul_cash _null_ _null_ _null_ ));
962962
DATA(insert OID = 863 ( int2_mul_cash PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "21 790" _null_ _null_ _null_ _null_ _null_ int2_mul_cash _null_ _null_ _null_ ));
963963
DATA(insert OID = 3344 ( cash_mul_int8 PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "790 20" _null_ _null_ _null_ _null_ _null_ cash_mul_int8 _null_ _null_ _null_ ));
@@ -4903,6 +4903,7 @@ DESCR("GIN support");
49034903
DATA(insert OID = 3301 ( jsonb_concat PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 3802 "3802 3802" _null_ _null_ _null_ _null_ _null_ jsonb_concat _null_ _null_ _null_ ));
49044904
DATA(insert OID = 3302 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 3802 "3802 25" _null_ _null_ _null_ _null_ _null_ jsonb_delete _null_ _null_ _null_ ));
49054905
DATA(insert OID = 3303 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 3802 "3802 23" _null_ _null_ _null_ _null_ _null_ jsonb_delete_idx _null_ _null_ _null_ ));
4906+
DATA(insert OID = 3343 ( jsonb_delete PGNSP PGUID 12 1 0 25 0 f f f f t f i s 2 0 3802 "3802 1009" "{3802,1009}" "{i,v}" "{from_json,path_elems}" _null_ _null_ jsonb_delete_array _null_ _null_ _null_ ));
49064907
DATA(insert OID = 3304 ( jsonb_delete_path PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 3802 "3802 1009" _null_ _null_ _null_ _null_ _null_ jsonb_delete_path _null_ _null_ _null_ ));
49074908
DATA(insert OID = 3305 ( jsonb_set 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_set _null_ _null_ _null_ ));
49084909
DESCR("Set part of a jsonb");

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3095,6 +3095,24 @@ select '["a","b","c"]'::jsonb - -4;
30953095
["a", "b", "c"]
30963096
(1 row)
30973097

3098+
select '{"a":1 , "b":2, "c":3}'::jsonb - '{b}'::text[];
3099+
?column?
3100+
------------------
3101+
{"a": 1, "c": 3}
3102+
(1 row)
3103+
3104+
select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[];
3105+
?column?
3106+
----------
3107+
{"a": 1}
3108+
(1 row)
3109+
3110+
select '{"a":1 , "b":2, "c":3}'::jsonb - '{}'::text[];
3111+
?column?
3112+
--------------------------
3113+
{"a": 1, "b": 2, "c": 3}
3114+
(1 row)
3115+
30983116
select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
30993117
jsonb_set
31003118
--------------------------------------------------------------------------

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -777,6 +777,10 @@ select '["a","b","c"]'::jsonb - -2;
777777
select'["a","b","c"]'::jsonb--3;
778778
select'["a","b","c"]'::jsonb--4;
779779

780+
select'{"a":1 , "b":2, "c":3}'::jsonb-'{b}'::text[];
781+
select'{"a":1 , "b":2, "c":3}'::jsonb-'{c,b}'::text[];
782+
select'{"a":1 , "b":2, "c":3}'::jsonb-'{}'::text[];
783+
780784
select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb,'{n}','[1,2,3]');
781785
select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb,'{b,-1}','[1,2,3]');
782786
select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb,'{d,1,0}','[1,2,3]');

0 commit comments

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