Appending new object to JSONB in postgresql

Question

I trying to add new object to my table in one column called PARAM, inside of that column I have:

{"array1": [
    {"array2": [
        {"objId": 1, "data": false, "repeat": 1},
        {"objId": 2, "data": false, "repeat": 1}
    ]}
]}

I want to add new object to array2 I am using that query

UPDATE table
SET param = jsonb_set(
        param::jsonb,
        array['array1'],
        (param->'array1'->>'array2')::jsonb || jsonb_build_object('objId', '3','data', 'false','repeat', '1')::jsonb)
WHERE ...

but instead getting

{"array1": [
    {"array2": [
        {"objId": 1, "data": false, "repeat": 1},
        {"objId": 2, "data": false, "repeat": 1},
        {"objId": 3, "data": false, "repeat": 1}
    ]}
]}

my PARAM become null. Anyone can solve that?

I have based my solution on that topic

score 1 · Answer 1 · edited Jun 05 '20 at 18:01

1

Try this:

select 
      jsonb_set(
                param::jsonb,
                '{array1,0,array2}',
                jsonb_extract_path(param, 'array1','0','array2')::jsonb ||
                jsonb_build_object('objId', '3','data', 'false','repeat', '1')::jsonb
               )

you can put where clause as per your requirement

edited Jun 05 '20 at 18:01

Barbaros Özhan

59,113
10
31
55

answered Jun 03 '20 at 17:53

Akhilesh Mishra

5,876
3
16
32

Appending new object to JSONB in postgresql

1 Answers1