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
