jsonb - jsonb operators

On Fri, Jan 15, 2016 at 7:43 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:

Hi all,

I was just looking through the new jsonb operators in the 9.5 release, and was wondering if there's any future intention to add a delete operator that removes element/pair matches? I.e. some sort of top-level "jsonb - jsonb" operator, e.g.

# select '{"a":1, "b":2}'::jsonb - '{"b":2, "a":4}'::jsonb;
?column?
----------
{"a": 1}
(1 row)

Or would this behaviour be classed as incorrect in some way?

It makes sense for simple json objects in your contrived example.

How would you define complex structures? What happens when the key
matches but not the value?

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

----- Original Message -----

From: Merlin Moncure <mmoncure@gmail.com>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Sent: Friday, 15 January 2016, 14:50
Subject: Re: [HACKERS] jsonb - jsonb operators

On Fri, Jan 15, 2016 at 7:43 AM, Glyn Astill <glynastill@yahoo.co.uk>
wrote:

Hi all,

I was just looking through the new jsonb operators in the 9.5 release, and

was wondering if there's any future intention to add a delete operator that
removes element/pair matches? I.e. some sort of top-level "jsonb -
jsonb" operator, e.g.

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

'{"b":2, "a":4}'::jsonb;

?column?
----------
{"a": 1}
(1 row)

Or would this behaviour be classed as incorrect in some way?

It makes sense for simple json objects in your contrived example.

How would you define complex structures? What happens when the key
matches but not the value?

I was thinking of operating on the top level only, if the value doesn't match the pair isn't removed.

For anything more complex you'd have to have more knowledge of the objects, so functions like jsonb_delete_path and jsonb_set make more sense.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

Dmitry Dolgov <9erthalion6@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------------------------------------
On Mon, 18/1/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Dmitry Dolgov <9erthalion6@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1/15/16, Glyn Astill <glynastill@yahoo.co.uk> wrote:

Hi all,

I was just looking through the new jsonb operators in the 9.5 release, and
was wondering if there's any future intention to add a delete operator that
removes element/pair matches? I.e. some sort of top-level "jsonb - jsonb"
operator, e.g.

# select '{"a":1, "b":2}'::jsonb - '{"b":2, "a":4}'::jsonb;
?column?
----------
{"a": 1}
(1 row)

Or would this behaviour be classed as incorrect in some way?

Thanks
Glyn

I thing the operator 'jsonb-jsonb' behavior in such case is not obvious.
How to understand the result is not like that:

# select '{"a":1, "b":2}'::jsonb - '{"b":2, "a":4}'::jsonb;
?column?
----------
{"a": -3, "b": 0}
(1 row)

P.S.: I guess an _operator_ jsonb+jsonb doesn't exist by the same way…
--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 18, 2016 at 10:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dmitry Dolgov <9erthalion6@gmail.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.

That's a deal breaker for introducing proposed functionality against
an operator. Maybe a function is a better choice.

I'm also squarely in the camp of "don't break userspace", meaning that
usefulness not enough of reason for changing in-place behaviors.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers