Postgresql: Append object to a list

Question

I have data that has a key as string and value is a list of json, looks like

ID|data
A1|{key1:[{k1:v1,k2:v2},{{k3:v3,k4:v4}]}

I want to append json, say, {k9:v9,k7:v6} to this list for the key1, something like-

ID|data
A1|{key1:[{k1:v1,k2:v2},{{k3:v3,k4:v4},{k9:v9,k7:v6}]}

I have tried jsonb_set and other functions but they were of no use, example-

UPDATE tbl_name
SET data = jsonb_set(data,'{key1,1}','{k9:v9,k7:v6}'::jsonb) where ID = 'A1'

Barbaros Özhan · Accepted Answer · 2020-08-31 15:05:01Z

2

You need to use jsonb_insert() function in order to append that part, after fixing the format of JSONB value ( otherwise you'd get "ERROR: invalid input syntax for type json" ) :

UPDATE tbl_name
   SET data = jsonb_insert(data,'{key1,1}','{"k9":"v9","k7":"v6"}'::jsonb) 
 WHERE ID = 'A1'

Demo

answered Aug 31, 2020 at 15:05

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: Append object to a list

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