postgres - syntax for updating a jsonb array

Question

I'm struggling to find the right syntax for updating an array in a jsonb column in postgres 9.6.6

Given a column "comments", with this example:

[
  {
    "Comment": "A",
    "LastModified": "1527579949"
  },
  {
    "Comment": "B",
    "LastModified": "1528579949"
  },
  {
    "Comment": "C",
    "LastModified": "1529579949"
  }
]

If I wanted to append Z to each comment (giving AZ, BZ, CZ).

I know I need to use something like jsonb_set(comments, '{"Comment"}',

Any hints on finishing this off?

Thanks.

sticky bit · Accepted Answer · 2018-06-03 17:56:16Z

Try:

UPDATE elbat
       SET comments = array_to_json(ARRAY(SELECT jsonb_set(x.original_comment,
                                                           '{Comment}',
                                                           concat('"',
                                                                  x.original_comment->>'Comment',
                                                                  'Z"')::jsonb)
                                                 FROM (SELECT jsonb_array_elements(elbat.comments) original_comment) x))::jsonb;

It uses jsonb_array_elements() to get the array elements as set, applies the changes on them using jsonb_set(), transforms this to an array and back to json with array_to_json().

But that's an awful lot of work. OK, maybe there is a more elegant solution, that I didn't find. But since your JSON seems to have a fixed schema anyway, I'd recommend a redesign to do it the relational way and have a simple table for the comments plus a linking table for the objects the comment is on. The change would have been very, very easy in such a model for sure.

I agree that relational would make more sense. This is an existing database and my first encounter with postgres' jsonb data type.

klin · Accepted Answer · 2018-06-03 18:18:57Z

Find a query returning the expected result:

select jsonb_agg(value || jsonb_build_object('Comment', value->>'Comment' || 'Z'))
from my_table
cross join jsonb_array_elements(comments);

                                                                      jsonb_agg                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 [{"Comment": "AZ", "LastModified": "1527579949"}, {"Comment": "BZ", "LastModified": "1528579949"}, {"Comment": "CZ", "LastModified": "1529579949"}]
(1 row)

Create a simple SQL function based of the above query:

create or replace function update_comments(jsonb)
returns jsonb language sql as $$
    select jsonb_agg(value || jsonb_build_object('Comment', value->>'Comment' || 'Z'))
    from jsonb_array_elements($1)
$$;

Use the function:

update my_table
set comments = update_comments(comments);

DbFiddle.

Collectives™ on Stack Overflow

postgres - syntax for updating a jsonb array

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related