From d00ca333c338b40911e89939c3cc771025978595 Mon Sep 17 00:00:00 2001 From: Magnus Hagander Date: Wed, 18 Jan 2017 21:37:59 +0100 Subject: [PATCH] Implement array version of jsonb_delete and operator This makes it possible to delete multiple keys from a jsonb value by passing in an array of text values, which makes the operaiton much faster than individually deleting the keys (which would require copying the jsonb structure over and over again. Reviewed by Dmitry Dolgov and Michael Paquier --- doc/src/sgml/func.sgml | 8 +++ src/backend/utils/adt/jsonfuncs.c | 86 +++++++++++++++++++++++++++++ src/include/catalog/pg_operator.h | 2 + src/include/catalog/pg_proc.h | 3 +- src/test/regress/expected/jsonb.out | 18 ++++++ src/test/regress/sql/jsonb.sql | 4 ++ 6 files changed, 120 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index eb1b6984bf..2504a466e6 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10843,6 +10843,14 @@ table2-mapping on their key value. '{"a": "b"}'::jsonb - 'a' + + - + text[] + Delete multiple key/value pairs or string + elements from left operand. Key/value pairs are matched based + on their key value. + '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] + - integer diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 58c721c074..d624fdbf79 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -3437,6 +3437,92 @@ jsonb_delete(PG_FUNCTION_ARGS) PG_RETURN_JSONB(JsonbValueToJsonb(res)); } +/* + * SQL function jsonb_delete (jsonb, variadic text[]) + * + * return a copy of the jsonb with the indicated items + * removed. + */ +Datum +jsonb_delete_array(PG_FUNCTION_ARGS) +{ + Jsonb *in = PG_GETARG_JSONB(0); + ArrayType *keys = PG_GETARG_ARRAYTYPE_P(1); + Datum *keys_elems; + bool *keys_nulls; + int keys_len; + JsonbParseState *state = NULL; + JsonbIterator *it; + JsonbValue v, + *res = NULL; + bool skipNested = false; + JsonbIteratorToken r; + + if (ARR_NDIM(keys) > 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + + if (JB_ROOT_IS_SCALAR(in)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot delete from scalar"))); + + if (JB_ROOT_COUNT(in) == 0) + PG_RETURN_JSONB(in); + + deconstruct_array(keys, TEXTOID, -1, false, 'i', + &keys_elems, &keys_nulls, &keys_len); + + if (keys_len == 0) + PG_RETURN_JSONB(in); + + it = JsonbIteratorInit(&in->root); + + while ((r = JsonbIteratorNext(&it, &v, skipNested)) != 0) + { + skipNested = true; + + if ((r == WJB_ELEM || r == WJB_KEY) && v.type == jbvString) + { + int i; + bool found = false; + + for (i = 0; i < keys_len; i++) + { + char *keyptr; + int keylen; + + if (keys_nulls[i]) + continue; + + keyptr = VARDATA_ANY(keys_elems[i]); + keylen = VARSIZE_ANY_EXHDR(keys_elems[i]); + if (keylen == v.val.string.len && + memcmp(keyptr, v.val.string.val, keylen) == 0) + { + found = true; + break; + } + } + if (found) + { + /* skip corresponding value as well */ + if (r == WJB_KEY) + JsonbIteratorNext(&it, &v, true); + + continue; + } + } + + res = pushJsonbValue(&state, r, r < WJB_BEGIN_ARRAY ? &v : NULL); + } + + Assert(res != NULL); + + PG_RETURN_JSONB(JsonbValueToJsonb(res)); +} + /* * SQL function jsonb_delete (jsonb, int) * diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index b1d25b5efb..45feb69b93 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1826,6 +1826,8 @@ DATA(insert OID = 3284 ( "||" PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_con DESCR("concatenate"); DATA(insert OID = 3285 ( "-" PGNSP PGUID b f f 3802 25 3802 0 0 3302 - - )); DESCR("delete object field"); +DATA(insert OID = 3398 ( "-" PGNSP PGUID b f f 3802 1009 3802 0 0 3343 - -)); +DESCR("delete object fields"); DATA(insert OID = 3286 ( "-" PGNSP PGUID b f f 3802 23 3802 0 0 3303 - - )); DESCR("delete array element"); DATA(insert OID = 3287 ( "#-" PGNSP PGUID b f f 3802 1009 3802 0 0 jsonb_delete_path - - )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 1a0eba3ca1..03f55a1cc5 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -957,7 +957,7 @@ DESCR("name of the current database"); 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_ )); DESCR("get the currently executing query"); -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_ )); +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_ )); 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_ )); 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_ )); 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"); 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_ )); 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_ )); 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_ )); +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_ )); 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_ )); 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_ )); DESCR("Set part of a jsonb"); diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index e2cb08a6fb..ba9b1d711e 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -3095,6 +3095,24 @@ select '["a","b","c"]'::jsonb - -4; ["a", "b", "c"] (1 row) +select '{"a":1 , "b":2, "c":3}'::jsonb - '{b}'::text[]; + ?column? +------------------ + {"a": 1, "c": 3} +(1 row) + +select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[]; + ?column? +---------- + {"a": 1} +(1 row) + +select '{"a":1 , "b":2, "c":3}'::jsonb - '{}'::text[]; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]'); jsonb_set -------------------------------------------------------------------------- diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 6b4c796992..eb65a38197 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -777,6 +777,10 @@ select '["a","b","c"]'::jsonb - -2; select '["a","b","c"]'::jsonb - -3; select '["a","b","c"]'::jsonb - -4; +select '{"a":1 , "b":2, "c":3}'::jsonb - '{b}'::text[]; +select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[]; +select '{"a":1 , "b":2, "c":3}'::jsonb - '{}'::text[]; + select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]'); select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]'); select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]'); -- 2.40.0