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

Barbaros Özhan · Accepted Answer · 2020-06-05 18:01:46Z

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 5, 2020 at 18:01

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jun 3, 2020 at 17:53

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Appending new object to JSONB in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related