Search code examples
arraysjsonpostgresqlappendjsonb

Postgresql: Append object to a list

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'
Solution

  • 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