Search code examples
sqlarrayspostgresqljsonb

How to remove/update a JSONB array element where key equals a value?

I'd like remove/replace an element from a JSONB array where a property is equal to a set value. I've found a number of functions that will accomplish this but I'd like to know if there's a way to do it without one as I have database restrictions?

Here's an example JSONB value:

[
  { "ID": "valuea" },
  { "ID": "valueb" },
  { "ID": "valuec" }
]

I'd like to remove the second array position where ID is equal to valueb with a single update statement. I'd imagine this could finding the position/order in the array, jsonb_set() to remove it.

It would also be helpful if there was a way to update the row and not just remove it. Likely a similar query, again with jsonb_set().

Solution

  • Unfortunately, there is no function to return the position of a JSON array element (yet) as of Postgres 15.

    To remove a single matching element:

    UPDATE tbl t
SET    js = t.js - (SELECT j.ord::int - 1
                    FROM   jsonb_array_elements(t.js) WITH ORDINALITY j(v,ord)
                    WHERE  j.v = '{"ID": "valueb"}'
                    LIMIT  1)
WHERE  t.js @> '[{"ID": "valueb"}]'   -- optional
AND    jsonb_typeof(t.js) = 'array';  -- optional

    This UPDATE uses a correlated subquery with jsonb_array_elements().
    About WITH ORDINALITY:

    Both WHERE clauses are optional.

    • Use the filter t.js @> '[{"ID": "valueb"}]' to suppress (potentially expensive!) empty updates and make good use of an existing GIN index on the jsonb column

    • Use the filter jsonb_typeof(t.js) = 'array' to only suppress errors from non-arrays.

    Note how the outer filter includes enclosing array decorators [], while the inner filter (after unnesting) does not.

    To remove all matching elements:

    UPDATE tbl t
SET    js = (SELECT jsonb_agg(j.v)
             FROM   jsonb_array_elements(t.js) j(v)
             WHERE  NOT j.v @> '{"ID": "valueb"}')
WHERE  t.js @> '[{"ID": "valueb"}]';

    fiddle

    The second query aggregates a new array from remaining elements.

    This time, the inner filter uses @> instead of = to allow for additional keys. Chose the appropriate filter.

    Aside: jsonb_set() might be useful additionally if the array in question is actually nested, unlike your example.