Thread: Re: Inconsistent Behavior in JSONB Numeric Array Deletion

"David E. Wheeler" <david@justatheory.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).
Throwsan 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

On Jun 11, 2025, at 17:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Ah. It’s a pity the existing operator behaves differently for different rhs operands. But maybe add a new one that’s
definedto operator on contents rather than keys/indexes and deprecate (or un-document) the content behavior in the `-`
operator?

Best,

David

I am certainly not tied to the '-' operator, but I think the ability to remove items from a numeric json array,  based
ona value would be something that would benefit many users. 

-----Original Message-----
From: David E. Wheeler <david@justatheory.com>
Sent: Wednesday, June 11, 2025 2:48 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>; Mark Dake <mark.drake@golden-hind.com>; pgsql-hackers@postgresql.org
Subject: Re: Inconsistent Behavior in JSONB Numeric Array Deletion

On Jun 11, 2025, at 17:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Ah. It’s a pity the existing operator behaves differently for different rhs operands. But maybe add a new one that’s
definedto operator on contents rather than keys/indexes and deprecate (or un-document) the content behavior in the `-`
operator?

Best,

Davi