Hi all,
I'd like to report what I believe is an inconsistency in the behavior of the
jsonb - operator when applied to numeric arrays.
Problem
PostgreSQL allows us to check for the presence of a scalar inside a JSONB
array:
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.
Proposal
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.
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.
_____
Happy to clarify further or contribute a patch.
Best regards,
Mark Drake
