Inserting new item to json object of nested arrays within JSONB_SET

Question

I have a table (log_table) and in this table, there is a nested array json field (activities). With using this activities field, I want to insert new field to nested array.

log_table:

- id:long
- activities:json
- date:timestamp

example activities field:

[ 
   { 
      "actionType":"NOTIFICATION",
      "items":null
   },
   { 
      "actionType":"MUTATION",
      "items":[ 
         { 
            "id":387015007,
            "name":"epic",
            "value":{ 
               "currency":"USD",
               "amount":1.76
            }
         },
         { 
            "id":386521039,
            "name":"test",
            "value":{ 
               "currency":"USD",
               "amount":1.76
            }
         }
      ]
   }
]

As query, I've tried:

update 
table_log m 
set activities = 
jsonb_set(
   m.activities::jsonb, 
   array['activities',(pos1-1)::text,'items',(pos-1)::text,'newId'],
   ((obj->>'id'))::jsonb 
)::json
from table_log l
cross join jsonb_array_elements(l.activities::jsonb) with ordinality arr1(elems, pos1)
cross join jsonb_array_elements(case elems->'items' when 'null' then '[null]' else elems->'items' end)  with ordinality arr(obj, pos)
where (obj->>'id')::int = 387015007 and l.id = m.id;

With this query, I got an error like below:

ERROR:  path element at position 1 is not an integer: "activities"

Expected Output:

[ 
   { 
      "actionType":"NOTIFICATION",
      "items":null
   },
   { 
      "actionType":"MUTATION",
      "items":[ 
         { 
            "id":387015007,
            "newId":387015007,
            "name":"epic",
            "value":{ 
               "currency":"USD",
               "amount":1.76
            }
         },
         { 
            "id":386521039,
            "name":"test",
            "value":{ 
               "currency":"USD",
               "amount":1.76
            }
         }
      ]
   }
]

Is there any suggestion?

Sha · Accepted Answer · 2020-02-09 11:08:33Z

1

Solved it like below:

update 
table_log m 
set activities = 
jsonb_set(
   m.activities::jsonb, 
   array[(pos1-1)::text,'items',(pos-1)::text,'newId'],
   ((obj->>'id'))::jsonb 
)::json
from table_log l
cross join jsonb_array_elements(l.activities::jsonb) with ordinality arr1(elems, pos1)
cross join jsonb_array_elements(case elems->'items' when 'null' then '[null]' else elems->'items' end)  with ordinality arr(obj, pos)
where (obj->>'id')::int = 387015007 and l.id = m.id;

So the problem was;

array['activities',...] -> 'activities' is redundant in here.

answered Feb 9, 2020 at 11:08

Sha

1,3012 gold badges24 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Inserting new item to json object of nested arrays within JSONB_SET

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