jsonb_delete with arrays
Attached is an implantation of jsonb_delete that instead of taking a single
key to remove accepts an array of keys (it still does just keys, so it's
using the - operator, it's not like the path delete function that also
takes an array, but uses a different operator).
In some simple testing of working through a real world usecases where we
needed to delete 7 keys from jsonb data, it shows approximately a 9x
speedup over calling the - operator multiple times. I'm guessing since we
copy a lot less and don't have to re-traverse the structure.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
jsonb_delete_array.patchtext/x-patch; charset=US-ASCII; name=jsonb_delete_array.patchDownload
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 17ee4e4..a7c92d6 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, 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 26fa618..347b2e1 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 17ec71d..80c9ddc 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4895,6 +4895,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 0 0 f f f f t f i s 2 0 3802 "3802 1009" _null_ _null_ _null_ _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 470d5b1..d6af415 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]');
On 15 November 2016 at 22:53, Magnus Hagander <magnus@hagander.net> wrote:
Attached is an implantation of jsonb_delete that instead of taking a
single key to remove accepts an array of keys (it still does just keys, so
it's using the - operator, it's not like the path delete function that also
takes an array, but uses a different operator).
In some simple testing of working through a real world usecases where we
needed to delete 7 keys from jsonb data, it shows approximately a 9x
speedup over calling the - operator multiple times. I'm guessing since we
copy a lot less and don't have to re-traverse the structure.
I wonder, is it worth it to create some sort of helper function to handle
both
deleting one key and deleting an array of keys (like `setPath` for
`jsonb_set`,
`jsonb_insert` and `jsonb_delete_path`)? At first glance it looks like
`jsonb_delete` and `jsonb_delete_array` can reuse some code.
Speaking about the performance I believe it's the same problem as here [1]/messages/by-id/1566eab8731.10193ac585742.5467876610052746443@zohocorp.com,
since for each key the full jsonb will be decompressed. Looks like we need a
new set of functions to read/update/delete an array of elements at once.
[1]: /messages/by-id/1566eab8731.10193ac585742.5467876610052746443@zohocorp.com
/messages/by-id/1566eab8731.10193ac585742.5467876610052746443@zohocorp.com
On Mon, Nov 21, 2016 at 5:05 AM, Dmitry Dolgov <9erthalion6@gmail.com>
wrote:
On 15 November 2016 at 22:53, Magnus Hagander <magnus@hagander.net>
wrote:
Attached is an implantation of jsonb_delete that instead of taking a
single key to remove accepts an array of keys (it still does just keys, so
it's using the - operator, it's not like the path delete function that also
takes an array, but uses a different operator).In some simple testing of working through a real world usecases where we
needed to delete 7 keys from jsonb data, it shows approximately a 9x
speedup over calling the - operator multiple times. I'm guessing since we
copy a lot less and don't have to re-traverse the structure.I wonder, is it worth it to create some sort of helper function to handle
both
deleting one key and deleting an array of keys (like `setPath` for
`jsonb_set`,
`jsonb_insert` and `jsonb_delete_path`)? At first glance it looks like
`jsonb_delete` and `jsonb_delete_array` can reuse some code.Speaking about the performance I believe it's the same problem as here [1],
since for each key the full jsonb will be decompressed. Looks like we need
a
new set of functions to read/update/delete an array of elements at once.[1]: /messages/by-id/1566eab8731.10193ac585742.
5467876610052746443%40zohocorp.com
It can be partially related, but the usecase itself had jsonb in memory
only and never stored on disk, so it's not the decompression itself.
Shouldn't be deep parsing either as we just copy the data over. But it's a
different angle on the same core problem, I think, which comes from the
fact that jsonb is just "one value".
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attached is an implantation of jsonb_delete that instead of taking a
single key to remove accepts an array of keys
Since I already saw this patch, here is my small review.
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.
* 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}')`.
I've attached a patch with these modifications. What do you think?
Attachments:
jsonb_delete_worker.patchtext/x-patch; charset=US-ASCII; name=jsonb_delete_worker.patchDownload
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 17ee4e4..aa8156e 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -149,6 +149,11 @@ static void setPathArray(JsonbIterator **it, Datum *path_elems,
int level, Jsonb *newval, uint32 nelems, int op_type);
static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
+/* common workers for jsonb_delete functions */
+static Datum jsonb_delete_worker(Jsonb *in, Datum *elems,
+ bool *nulls, int len);
+
+
/* state for json_object_keys */
typedef struct OkeysState
{
@@ -3395,14 +3400,8 @@ jsonb_delete(PG_FUNCTION_ARGS)
{
Jsonb *in = PG_GETARG_JSONB(0);
text *key = PG_GETARG_TEXT_PP(1);
- char *keyptr = VARDATA_ANY(key);
- int keylen = VARSIZE_ANY_EXHDR(key);
- JsonbParseState *state = NULL;
- JsonbIterator *it;
- JsonbValue v,
- *res = NULL;
- bool skipNested = false;
- JsonbIteratorToken r;
+ Datum *keys = (Datum *) palloc(sizeof(Datum));
+ bool *nulls = (bool *) palloc(sizeof(bool));
if (JB_ROOT_IS_SCALAR(in))
ereport(ERROR,
@@ -3412,21 +3411,95 @@ jsonb_delete(PG_FUNCTION_ARGS)
if (JB_ROOT_COUNT(in) == 0)
PG_RETURN_JSONB(in);
+ keys[0] = PointerGetDatum(key);
+ nulls[0] = FALSE;
+
+ return jsonb_delete_worker(in, keys, nulls, 1);
+}
+
+/*
+ * SQL function jsonb_delete (jsonb, 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;
+
+ 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);
+
+ return jsonb_delete_worker(in, keys_elems, keys_nulls, keys_len);
+}
+
+Datum
+jsonb_delete_worker(Jsonb *in, Datum *elems, bool *nulls, int len)
+{
+ JsonbParseState *state = NULL;
+ JsonbIterator *it;
+ JsonbValue v,
+ *res = NULL;
+ bool skipNested = false;
+ JsonbIteratorToken r;
+
+ if (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 && keylen == v.val.string.len &&
- memcmp(keyptr, v.val.string.val, keylen) == 0))
+ if ((r == WJB_ELEM || r == WJB_KEY) && v.type == jbvString)
{
- /* skip corresponding value as well */
- if (r == WJB_KEY)
- JsonbIteratorNext(&it, &v, true);
+ int i;
+ bool found = false;
- continue;
+ for (i = 0; i < len; i++)
+ {
+ char *keyptr;
+ int keylen;
+
+ if (nulls[i])
+ continue;
+
+ keyptr = VARDATA_ANY(elems[i]);
+ keylen = VARSIZE_ANY_EXHDR(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);
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 26fa618..347b2e1 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 047a1ce..535ce6e 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4897,6 +4897,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 470d5b1..d6af415 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]');
On Sun, Dec 18, 2016 at 1:27 AM, Dmitry Dolgov <9erthalion6@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.
* 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'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.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 17, 2017 at 8:25 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Sun, Dec 18, 2016 at 1:27 AM, Dmitry Dolgov <9erthalion6@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/
Attachments:
jsonb_delete_array_2.patchtext/x-patch; charset=US-ASCII; name=jsonb_delete_array_2.patchDownload
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]');
On Tue, Jan 17, 2017 at 8:45 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Tue, Jan 17, 2017 at 8:25 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:On Sun, Dec 18, 2016 at 1:27 AM, Dmitry Dolgov <9erthalion6@gmail.com>
wrote:* 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.
Yes that should be enough:
=# select jsonb_delete('{"a":1 , "b":2, "c":3}', 'a', 'b', 'c');
jsonb_delete
--------------
{}
(1 row)
=# select '{"a":1 , "b":2, "c":3}'::jsonb - '{a,b}'::text[];
?column?
----------
{"c": 3}
(1 row)
That's a nice bonus, perhaps that's not worth documenting as most
users will likely care only about 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?
I am not seeing other places to update, thanks.
Another victim of 352a24a... Your patch is failing to apply because
now the headers of the functions is generated automatically. And the
OIDs have been taken recently. I have fixed that to test your patch,
the result is attached. The patch is marked as ready for committer.
--
Michael
Attachments:
jsonb_delete_array_3.patchtext/plain; charset=US-ASCII; name=jsonb_delete_array_3.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 10e31868ba..af3d2aa6a8 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 58c721c074..d624fdbf79 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 b1d25b5efb..9b7795dd67 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 = 3354 ( "-" PGNSP PGUID b f f 3802 1009 3802 0 0 3353 - -));
+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 42f36891af..02c9ab03d2 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -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 = 3353 ( 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]');
On Wed, Jan 18, 2017 at 5:49 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Tue, Jan 17, 2017 at 8:45 PM, Magnus Hagander <magnus@hagander.net>
wrote:On Tue, Jan 17, 2017 at 8:25 AM, Michael Paquier <
michael.paquier@gmail.com>
wrote:
On Sun, Dec 18, 2016 at 1:27 AM, Dmitry Dolgov <9erthalion6@gmail.com>
wrote:* 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 whichcase
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 VARIADICkeyword
in the caller. Or by just using the operator.
Yes that should be enough:
=# select jsonb_delete('{"a":1 , "b":2, "c":3}', 'a', 'b', 'c');
jsonb_delete
--------------
{}
(1 row)
=# select '{"a":1 , "b":2, "c":3}'::jsonb - '{a,b}'::text[];
?column?
----------
{"c": 3}
(1 row)
That's a nice bonus, perhaps that's not worth documenting as most
users will likely care only about 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?
I am not seeing other places to update, thanks.
Another victim of 352a24a... Your patch is failing to apply because
now the headers of the functions is generated automatically. And the
OIDs have been taken recently. I have fixed that to test your patch,
the result is attached. The patch is marked as ready for committer.
Thanks! I had already rebased it, so I pushed that version (picked
different oids).
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/