To remove all elements from the column images
(holding a json array) where 'id'
is 'note_1'
:
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()
in a subquery using an implicit JOIN LATERAL
for the set-returning function. Details:
- JOIN to the base table and add another
WHERE
condition using json_array_length()
to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb
and additional jsonb
operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;
Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb
, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
'{"id":"note_1"}'
or delete any element from the array that has'note_1'
as key or value?{"id": "note_1", "full": "<url>", "thumb": "<url>"}
where id = "note_1"