On Jun 7, 2025, at 16:20, Mark Dake <mark.dr...@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 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
signature.asc
Description: Message signed with OpenPGP
