Query to update an array and insert new element

SQL++
1

Hi,

I have a document structured as below. I am trying to construct a query which will replace my 2nd array element with some string and add a 3rd element in the actions array.

Please help with the query.

UPDATE business_rules AS act USE KEYS [“internal_rt”]
set act.actions[1].id=“OptDM1”

UPDATE business_rules AS act USE KEYS [“internal_rt”]
set act.actions=ARRAY_INSERT(act.actions,3, ‘{“id”: “OptTele”,“label”: “Opt Tele”}’)

{
“business_rules”: {
“actions”: [
{
“id”: “OptDM”,
“label”: “Opt Direct Mail”
},
{
“id”: “OptSMS”,
“label”: “Opt SMS”
}
],
“deployed”: “2021-03-01T13:03:56.957-05:00”
}
}

2 
INSERT INTO business_rules VALUES("internal_rt", { "actions": [ { "id": "OptDM", "label": "Opt Direct Mail" }, { "id": "OptSMS", "label": "Opt SMS" } ], "deployed": "2021-03-01T13:03:56.957-05:00" } );

UPDATE business_rules AS act USE KEYS ["internal_rt"] SET act.actions[1].id="OptDM1";

UPDATE business_rules AS act USE KEYS ["internal_rt"] set act.actions=ARRAY_APPEND(act.actions,{"id": "OptTele","label": "Opt Tele"});

SELECT act.* FROM  business_rules AS act USE KEYS ["internal_rt"];

You can’t insert beyond array by skipping position. Use append

   UPDATE business_rules AS act USE KEYS ["internal_rt"] 
    SET act.actions=ARRAY_APPEND(act.actions,{"id": "OptTele","label": "Opt Tele"}), act.actions[1].id="OptDM1";
3

Thank you so much. It worked