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

Question

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

I assume there will be more keys than just 'ID' per array element? (Else, just drop the redundant key and make it an array of strings.) Your version of Postgres? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 9, 2023 at 3:38

Erwin Brandstetter · Accepted Answer · 2023-01-09 05:35:56Z

4

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.

edited Jan 9, 2023 at 5:35

answered Jan 9, 2023 at 5:21

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pilotguy Over a year ago

Awesome. I was on the right path but I was writing with ordinality completely wrong. I appreciate your insight and support!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related