Обсуждение: jsonb - jsonb operators

Hi all,

I was just looking through the new jsonb operators in the 9.5 release, and was wondering if there's any future
intentionto 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

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
intentionto 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

----- 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_setmake more sense.
 

<div dir="ltr">> <span style="font-size:13px"> if there's any future intention to add a delete operator that removes
element/pairmatches?</span><div class="gmail_extra"><div class="gmail_quote"><br /></div><div class="gmail_quote">I
thinkthe 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
thesefunctions (at least for jsonbx). But of course this function should be quite simple and consider only full
key/valuematching as a target.</div></div></div> 

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

--------------------------------------------
On Mon, 18/1/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Subject: Re: [HACKERS] jsonb - jsonb operatorsTo: "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:50Dmitry Dolgov <9erthalion6@gmail.com>writes:>> if
there'sany future intention toadd a delete operator that removes>element/pair matches?>I think the operator (jsonb -
jsonb)is logical because wehave a shallow> concatenation function(something like a "union" operation), but wehave>
nothinglike "setdifference" and "intersection" functions.Actually, I> thought to implement thesefunctions (at least for
jsonbx).But of course> this function should be quite simple andconsider only full key/value> matchingas a target.I
amwaryof this proposal because it seems to be takinglittleaccount of the fact that there*already is* a jsonb minus
operator,twoofthem in fact.  For exampleregression=# select'["a","b","c"]'::jsonb- 'b';  ?column?  ------------
["a","c"](1row)regression=# select '{"a":1,"b":2}'::jsonb - 'b';?column? ----------{"a": 1}(1 row)The proposed
full-matchsemanticsdon't seem to me to be consistent withthe way that the existing operator works.Another rather nasty
problemisthat the latter case works at all,ie theparser will decide the unknown literal is "text"so that it canapply
"jsonb-text", there being no other plausible choice.  Iftherewere a "jsonb - jsonb"operator, the parser would prefer
thatone, dueto its heuristic about assuming that an unknownliteral is of the sametype as the otheroperator input.  So
addingsuch an operator will almostcertainly break queries that work in 9.5. Maybe it's worth adding oneanyway, butI
don'tthink the case for its usefulness has beenprovento the point where we should createcompatibility 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
deleteon 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

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

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