6

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');
2
  • 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? Dec 6, 2014 at 1:21
  • @ErwinBrandstetter, delete {"id": "note_1", "full": "<url>", "thumb": "<url>"} where id = "note_1"
    – TSK
    Dec 8, 2014 at 17:26

1 Answer 1

10

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:

4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.