On Tue, Jan 17, 2017 at 8:45 PM, Magnus Hagander <mag...@hagander.net> wrote:
> 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:
>> > * 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
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]');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
