How to remove object from json array?

Question

My table:

CREATE TABLE items (
 id BIGINT PRIMARY KEY NOT NULL,
 name VARCHAR,
 images json
);

images format:

[
 {
 "id": "owner",
 "full": "<url>",
 "thumb": "<url>"
 },
 {
 "id": "note_0",
 "full": "<url>",
 "thumb": "<url>"
 },
 {
 "id": "note_1",
 "full": "<url>",
 "thumb": "<url>"
 },
 {
 "id": "note_2",
 "full": "<url>",
 "thumb": "<url>"
 }
]

I need something like this:

UPDATE items SET images = delete(images, 'note_1');

• Erwin Brandstetter: Please be more specific: do you mean '{"id":"note_1"}' or delete any element from the array that has 'note_1' as key or value?
• TSK: @ErwinBrandstetter, delete {"id": "note_1", "full": "<url>", "thumb": "<url>"} where id = "note_1"

Answer 1

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

1. Unnest the JSON array with json_array_elements() in a subquery using an implicit JOIN LATERAL for the set-returning function. Details:
   • PostgreSQL joining using JSONB
   • How to turn json array into postgres array?
2. 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:

• "CREATE INDEX ... USING VODKA"

• CommonSenseCode: what does items cand mean
• Erwin Brandstetter: @commonSenseCode: FROM items cand is short syntax for FROM items AS cand - cand is a table alias. The AS keyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
• Allen: yer a wizard Erwin
• toxaq: The 9.3 version (at least) doesn't appear to work if you're removing the last item from an array?