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().

CodePudding user response：

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:

• PostgreSQL unnest() with element number

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.