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
