RE: Inconsistent Behavior in JSONB Numeric Array Deletion

Sorry, not a 'C' coder. A man must know his limits. ☹

-----Original Message-----
From: David E. Wheeler <david@justatheory.com>
Sent: Wednesday, June 11, 2025 11:49 AM
To: Mark Dake <mark.drake@golden-hind.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Inconsistent Behavior in JSONB Numeric Array Deletion

On Jun 7, 2025, at 16:20, Mark Dake <mark.drake@golden-hind.com> wrote:

> Support a jsonb - jsonb operator where, if the RHS is a scalar that appears in the LHS array, the operator removes
allmatching values: 
> SELECT jsonb('[2,3,1]') - to_jsonb(1);
> -- Expected: [2, 3]
> This would mirror similar behavior in many application languages and allow value-based deletion from JSON arrays
withoutcasting back to SQL arrays or using procedural workarounds. 

FWIW, this behavior exists using text values:

david=# select '["a", "b", "c", "b"]'::jsonb - 'b';
  ?column?
------------
 ["a", "c"]

But I take your point about using a JSONB value as the second argument. I wonder if it might be slightly confusing,
though.The `-` operator is already pretty overloaded with varying behavior based on the type of the right operand, but
maybethat ship has sunk. 

>
> Impact
> The absence of this capability creates a gap in value-level JSONB manipulation. Developers often have to resort to:
>     • Procedural code in PL/pgSQL
>     • Transforming JSONB arrays into SQL arrays (with limited type support)
>     • Writing client-side logic
> Adding support for this behavior would simplify many API use cases involving JSON state manipulation.

I like the idea, we just may want to muck with the semantics a bit. Do you have a patch to share?

Best,

David