Re: Inconsistent Behavior in JSONB Numeric Array Deletion

"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> Bear in mind that `-` currently does both. Of the three current variants, the first two delete from an array by value:

> * jsonb - text: Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.

> * jsonb - text[] → jsonb: Deletes all matching keys or array elements from the left operand.

> * jsonb - integer → jsonb: Deletes the array element with specified index (negative integers count from the end). Throws an error if JSON value is not an array.

> Before I went and looked it up, I was also thinking this could use a different operator. But it’s already a bit overloaded, alas. So I could see the new behavior being:

> * jsonb - jsonb → jsonb: Deletes the array element with specified value. Throws an error if JSON value is not an array.

I fear that that would cause some problems. Consider

regression=# select '["foo", "bar"]'::jsonb - 'bar';
?column?
----------
["foo"]
(1 row)

Right now we resolve the unlabeled literal as type text.
But if jsonb - jsonb existed, we'd decide it's jsonb, thanks
to the heuristic that prefers same-type-as-the-other-input
(rule 2a at [1]). So it's pretty nearly certain that
adding jsonb - jsonb would break some existing queries;
or worse, silently cause them to do something different.
Maybe that's acceptable, but it's a demerit of this proposal.

regards, tom lane

[1] https://www.postgresql.org/docs/current/typeconv-oper.html