Add new object to json array under jsonb column

0
votes

I have jsonb datatype column "payload" in postgres table which has the value as below:

{"testEvents": [
        {
            "id": 113068,
            "name1": "test",
            "count": 15
        },
        {
            "id": 113069,
            "name1": "test1",
            "count": 15
        }
    ]
}

Now I want to update the inner jsonarray by adding one more jsonobject to it. So, my results will be like

{"testEvents": [
        {
            "id": 113068,
            "name1": "test",
            "count": 15
        },
        {
            "id": 113069,
            "name1": "test1",
            "count": 15
        }
        ,
        {
            "id": 113070,
            "name1": "test2",
            "count": 18
        }
    ]
}

I tried the below query:

UPDATE table SET payload = payload ||'{"id":113070,"name1":"test2","count":18}';

But it is replacing the previous value. Since am new to this topic, can anyone please help with the right way to do it.

2
jsonpostgresqljsonbpostgresql-9.5

2 Answers

0
votes

You need to append the new value to the array ('testEvents') not to the complete JSON value. This can be done using jsonb_set() 

update the_table
  set payload = jsonb_set(payload, '{testEvents}', payload -> 'testEvents' || '{"id":113070,"name1":"test2","count":18}');

The part payload -> 'testEvents' || '{"id":113070,"name1":"test2","count":18}' appends the new value to the array and jsonb_set then replaces the array under testEvents with that new array.

0
votes

jsonb_insert() is your function

demo:db<>fiddle

UPDATE mytable
SET jsondata =
    jsonb_insert(jsondata, '{testEvents,0}', '{"id":113070, "name1":"test2","count":18}');

The second parameter defines the path where the new element has to be inserted. 0 is the first position.