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(at)justatheory(dot)com>
Sent: Wednesday, June 11, 2025 11:49 AM
To: Mark Dake <mark(dot)drake(at)golden-hind(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Inconsistent Behavior in JSONB Numeric Array Deletion

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

> Support a jsonb - jsonb operator where, if the RHS is a scalar that appears in the LHS array, the operator removes all matching 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 without casting 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 maybe that 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