Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save thomas-p-wilson/25e789b3618dd71647e5385d109f14fb to your computer and use it in GitHub Desktop.
Save thomas-p-wilson/25e789b3618dd71647e5385d109f14fb to your computer and use it in GitHub Desktop.
Remove a JSONB array element by object key in Posgres
UPDATE
<table> t
SET
<field> =(
SELECT
t2.<field> #- ARRAY[(
POSITION-1
)::TEXT] new_value
FROM
<table> t2 ,
jsonb_array_elements(t2.masks) WITH ORDINALITY arr(
elem,
POSITION
)
WHERE
t2.id = t.id
AND elem->>'<object_key>' = :value
)
WHERE
t.<field> @> CONCAT('[{"<object_key>":"', :value, '"}]')::JSONB
AND t.id IN (:ids);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment