On Tue, Jan 17, 2017 at 8:25 AM, Michael Paquier <michael.paqu...@gmail.com> wrote:
> On Sun, Dec 18, 2016 at 1:27 AM, Dmitry Dolgov <9erthali...@gmail.com> > wrote: > > Speaking about implementation of `jsonb_delete_array` - it's fine, but I > > would like to suggest two modifications: > > > > * create a separate helper function for jsonb delete operation, to use > it in > > both `jsonb_delete` and `jsonb_delete_array`. It will help to concentrate > > related logic in one place. > > I am not sure that it is much a win as the code loses readability for > a minimal refactoring. What would have been nice is to group as well > jsonb_delete_idx but handling of the element deletion is really > different there. > I agree with that. I agree with investigating it as an option, but I think the lost readability is worse. > > > * use variadic arguments for `jsonb_delete_array`. For rare cases, when > > someone decides to use this function directly instead of corresponding > > operator. It will be more consistent with `jsonb_delete` from my point of > > view, because it's transition from `jsonb_delete(data, 'key')` to > > `jsonb_delete(data, 'key1', 'key2')` is more smooth, than to > > `jsonb_delete(data, '{key1, key2}')`. > > That's a good idea. > I can see the point of that. In the particular usecase I built it for originally though, the list of keys came from the application, in which case binding them as an array was a lot more efficient (so as not to require a whole lot of different prepared statements, one for each number of parameters). But that should be workaround-able using the VARIADIC keyword in the caller. Or by just using the operator. > > I've attached a patch with these modifications. What do you think? > > Looking at both patches proposed, documentation is still missing in > the list of jsonb operators as '-' is missing for arrays. I am marking > this patch as waiting on author for now. > Added in updated patch. Do you see that as enough, or do we need it in some more places in the docs as well? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 10e3186..af3d2aa 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10845,6 +10845,14 @@ table2-mapping </row> <row> <entry><literal>-</literal></entry> + <entry><type>text[]</type></entry> + <entry>Delete multiple key/value pairs or <emphasis>string</emphasis> + elements from left operand. Key/value pairs are matched based + on their key value.</entry> + <entry><literal>'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] </literal></entry> + </row> + <row> + <entry><literal>-</literal></entry> <entry><type>integer</type></entry> <entry>Delete the array element with specified index (Negative integers count from the end). Throws an error if top level diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 58c721c..d624fdb 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -3438,6 +3438,92 @@ jsonb_delete(PG_FUNCTION_ARGS) } /* + * 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) * * return a copy of the jsonb with the indicated item diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index aeb7927..0be5e26 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1820,6 +1820,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 = 3344 ( "-" 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 37e022d..be6cffd 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4899,6 +4899,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/include/utils/jsonb.h b/src/include/utils/jsonb.h index ad24a98..b2607ad 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -404,6 +404,7 @@ extern Datum jsonb_concat(PG_FUNCTION_ARGS); /* deletion */ extern Datum jsonb_delete(PG_FUNCTION_ARGS); +extern Datum jsonb_delete_array(PG_FUNCTION_ARGS); extern Datum jsonb_delete_idx(PG_FUNCTION_ARGS); extern Datum jsonb_delete_path(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index e2cb08a..ba9b1d7 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 6b4c796..eb65a38 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]');
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers