Re: Inconsistent Behavior in JSONB Numeric Array Deletion

On Tue, Jun 10, 2025 at 4:52 PM Mark Dake <mark(dot)drake(at)golden-hind(dot)com> wrote:
> SELECT jsonb('[2,3,1]') @> to_jsonb(1);
> -- Returns true
>
> However, when attempting to remove that value from the array using -, the operation fails:
> SELECT jsonb('[2,3,1]') - to_jsonb(1);
> -- ERROR: operator does not exist: jsonb - jsonb
> -- HINT: You might need to add explicit type casts.
>
> This behavior differs from index-based removal:
> SELECT jsonb('[2,3,1]') - 1;
> -- Returns [2, 1]
>
> But logically, if @> to_jsonb(1) is true, then jsonb('[2,3,1]') - to_jsonb(1) should remove the value, not the index.

I don't see how you can say that there's anything inconsistent here.
The proposed behavior of the operator you want to add would be
inconsistent with the existing integer subtraction operator, because
the former would remove by value and the latter removes by index. But
there's no inconsistency right now because the operator you want to
add doesn't exist yet. And I don't think the fact that @>(jsonb,jsonb)
exists can reasonably be said to be inconsistent with the fact that
-(jsonb,jsonb) doesn't, either.

Now, none of that means that we couldn't define -(jsonb,jsonb) in the
manner you propose. But that's just a feature idea, not an
inconsistency.

--
Robert Haas
EDB: http://www.enterprisedb.com