Re: jsonb - jsonb operators

--------------------------------------------
On Mon, 18/1/16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Subject: Re: [HACKERS] jsonb - jsonb operators
To: "Dmitry Dolgov" <9erthalion6(at)gmail(dot)com>
Cc: "Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk>, "Merlin Moncure" <mmoncure(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Date: Monday, 18 January, 2016, 16:50

Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
writes:
>> if there's any future intention to
add a delete operator that removes
>
element/pair matches?

>
I think the operator (jsonb - jsonb) is logical because we
have a shallow
> concatenation function
(something like a "union" operation), but we
have
> nothing like "set
difference" and "intersection" functions.
Actually, I
> thought to implement these
functions (at least for jsonbx). But of course
> this function should be quite simple and
consider only full key/value
> matching
as a target.

I am
wary of this proposal because it seems to be taking
little
account of the fact that there
*already is* a jsonb minus operator,
two of
them in fact.  For example

regression=# select
'["a","b","c"]'::jsonb
- 'b';
  ?column? 
------------
["a",
"c"]
(1 row)

regression=# select '{"a":1,
"b":2}'::jsonb - 'b';

?column?
----------

{"a": 1}
(1 row)

The proposed full-match
semantics don't seem to me to be consistent with
the way that the existing operator works.

Another rather nasty problem
is that the latter case works at all,
ie the
parser will decide the unknown literal is "text"
so that it can
apply "jsonb -
text", there being no other plausible choice.  If
there
were a "jsonb - jsonb"
operator, the parser would prefer that one, due
to its heuristic about assuming that an unknown
literal is of the same
type as the other
operator input.  So adding such an operator will almost
certainly break queries that work in 9.5. 
Maybe it's worth adding one
anyway, but
I don't think the case for its usefulness has been
proven
to the point where we should create
compatibility issues to get it.

            regards, tom lane

In that case pehaps there is no need for an operator, but a function would be useful. Perhaps specifying the depth to delete on like Dimitri's key versions do?

I mocked up the top level version last year, like you say its trivial, but I find it useful. It's here https://github.com/glynastill/jsonb_delete