PostgreSQL 9.3 – How to Remove Object from JSON Array

jsonpostgresqlpostgresql-9.3

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');

Best Answer

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:
  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:

Related Question