Re: jsonb - jsonb operators

On Mon, Jan 18, 2016 at 10:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

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