UPDATE a json list type column in PostgreSQL

Question

I got a json type column named 'attr' which was used to store a list include some dict like [{"foo":1}, {"foo":2}, {"foo":3}].

Now I want to extend the list to [{"foo":1}, {"foo":2}, {"foo":3}, {"foo":4}]. Of course I can use a sql query like:

update tbl
set attr='[{"foo":1}, {"foo":2}, {"foo":3}, {"foo":4}]'::json;

However, I need execute it in my webserver. So I must select the former value, extend the list and then update into table.

I'm thinking if there is a way to use some postgresql function to do this like array_to_json or json_array_elements

I wrote this query:

update tbl
set attr = json_build_array(json_array_elements(attr), '{"foo":4}'::json);

But it got wrong result.

Erwin Brandstetter · Accepted Answer · 2015-07-10 23:01:39Z

2

For the data type json:

UPDATE tbl
SET    attr = (
   SELECT json_agg(elem)
   FROM  (
      SELECT elem
      FROM   json_array_elements(attr) elem
      UNION  ALL
      SELECT '{"foo":4}'::json
      ) sub
   )
WHERE  tbl_id = 123;  -- determine row to update

For the data type jsonb:

UPDATE tbl
SET    attr = (
   SELECT json_agg(elem)::jsonb
   FROM  (
      SELECT elem
      FROM   jsonb_array_elements(attr) elem
      UNION  ALL
      SELECT '{"foo":4}'::jsonb
      ) sub
   )
WHERE  tbl_id = 123;

The aggregate function json_agg() always returns json, even when called with jsonb input. So we need another cast to jsonb.

Obviously you want to limit your UPDATE to a specific row, so add a WHERE clause.

Don't go to the underlying table a second time in the subselect, just work with the value from the target row.

edited Jul 10, 2015 at 23:01

answered Jul 10, 2015 at 9:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

UPDATE a json list type column in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related