To update array if exist else insert new row

SQL++
1

Hi Folks,

Below is my array in bucket test123

{
“profiles”: [
{
“parent”: “USA”,
“child”: “NY1”
},
{
“parent”: “USA”,
“child”: “UAT”
},
{
“parent”: “CAN”,
“child”: “DEL”
},
{
“parent”: “USA”,
“child”: “HIL”
},
{
“parent”: “USA”,
“child”: “NJ6”
},
{
“parent”: “USA”,
“child”: “NJ7”
},
{
“parent”: “CAN”,
“child”: “BHL”
},
{
“parent”: “CAN”,
“child”: “RIL”
},
{
“parent”: “INT”,
“child”: “DLF”
},
{
“parent”: “CAN”,
“child”: “CNB”
},
{
“parent”: “USA”,
“child”: “SAM”
},
{
“parent”: “USA”,
“child”: “ERP”
},
{
“parent”: “CAN”,
“child”: “RBI”
},
{
“parent”: “USA”,
“child”: “TMT”
},
{
“parent”: “USA”,
“child”: “CSC”
},
{
“parent”: “USA”,
“child”: “AVI”
},
{
“parent”: “CAN”,
“child”: “HGD”
},
{
“parent”: “INT”,
“child”: “INT”
},
{
“parent”: “INT”,
“child”: “123”
},
{
“parent”: “USA”,
“child”: “SDG”
},
{
“parent”: “USA”,
“child”: “JHD”
},
{
“parent”: “USA”,
“child”: “222”
},
{
“parent”: “CAN”,
“child”: “QAY”
},
{
“parent”: “USA”,
“child”: “TES”
},
{
“parent”: “USA”,
“child”: “JKF”
},
{
“parent”: “INT”,
“child”: “AXA”
},
{
“parent”: “INT”,
“child”: “SKJ”
},
{
“parent”: “USA”,
“child”: “TYU”
},
{
“parent”: “USA”,
“child”: “PPP”
},
{
“parent”: “USA”,
“child”: “KSL”
},
{
“parent”: “INT”,
“child”: “ASD”
},
{
“parent”: “USA”,
“child”: “LLL”
},
{
“parent”: “USA”,
“child”: “NJ4”
},
{
“parent”: “CAN”,
“child”: “NJ3”
},
{
“parent”: “USA”,
“child”: “NJ2”
},
{
“parent”: “CAN”,
“child”: “CAN”
},
{
“parent”: “USA”,
“child”: “SAL”
},
{
“parent”: “USA”,
“child”: “ISS”
},
{
“parent”: “INT”,
“child”: “SAG”
},
{
“parent”: “CAN”,
“child”: “KJL”
},
{
“parent”: “CAN”,
“child”: “VBS”
},
{
“parent”: “INT”,
“child”: “NHG”
},
{
“parent”: “USA”,
“child”: “SUN”
},
{
“parent”: “CAN”,
“child”: “TTR”
},
{
“parent”: “USA”,
“child”: “WOP”
},
{
“parent”: “INT”,
“child”: “VUL”
},
{
“parent”: “CAN”,
“child”: “JKJ”
},
{
“parent”: “USA”,
“child”: “LOP”
},
{
“parent”: “USA”,
“child”: “RAV”
},
{
“parent”: “INT”,
“child”: “SAI”
},
{
“parent”: “CAN”,
“child”: “SAN”
},
{
“parent”: “USA”,
“child”: “SAT”
},
{
“parent”: “INT”,
“child”: “JHF”
},
{
“parent”: “CAN”,
“child”: “ERT”
},
{
“parent”: “CAN”,
“child”: “FGH”
},
{
“parent”: “USA”,
“child”: “PWD”
},
{
“parent”: “USA”,
“child”: “XYZ”
},
{
“parent”: “USA”,
“child”: “XZZ”
},
{
“parent”: “USA”,
“child”: “ZZZ”
},
{
“parent”: “USA”,
“child”: “BNK”
},
{
“parent”: “USA”,
“child”: “14B”
},
{
“parent”: “USA”,
“child”: “TOD”
},
{
“parent”: “USA”,
“child”: “RPU”
},
{
“parent”: “USA”,
“child”: “SUD”
},
{
“parent”: “USA”,
“child”: “LAC”
},
{
“parent”: “USA”,
“child”: “LA1”
},
{
“parent”: “USA”,
“child”: “VGO”
},
{
“parent”: “USA”,
“child”: “USA”
},
{
“parent”: “USA”,
“child”: “AVA”
},
{
“parent”: “USA”,
“child”: “PO4”
},
{
“parent”: “USA”,
“child”: “KSA”
},
{
“parent”: “USA”,
“child”: “PT4”
},
{
“parent”: “USA”,
“child”: “PKA”
},
{
“parent”: “USA”,
“child”: “AAG”
},
{
“parent”: “USA”,
“child”: “AXZ”
},
{
“parent”: “INT”,
“child”: “DPS”
},
{
“parent”: “USA”,
“child”: “WG4”
},
{
“parent”: “CAN”,
“child”: “KI1”
},
{
“parent”: “INT”,
“child”: “TRY”
},
{
“parent”: “USA”,
“child”: “DFD”
},
{
“parent”: “USA”,
“child”: “TET”
},
{
“parent”: “CAN”,
“child”: “TOM”
},
{
“parent”: “USA”,
“child”: “DEV”
},
{
“parent”: “USA”,
“child”: “POY”
},
{
“parent”: “USA”,
“child”: “NOM”
},
{
“parent”: “USA”,
“child”: “DPT”
},
{
“parent”: “USA”,
“child”: “YTR”
},
{
“parent”: “CAN”,
“child”: “ADD”
},
{
“parent”: “USA”,
“child”: “RRR”
},
{
“parent”: “INT”,
“child”: “F17”
},
{
“parent”: “CAN”,
“child”: “WM5”
},
{
“parent”: “USA”,
“child”: “UA1”
}
]
I want to update array value if exist else add new:
Below are my queries:
Update pricing_qa_1 USE KEYS ‘qqcmstr’
SET profiles= ARRAY_APPEND(IFMISSING(‘profiles’),“parent”:“CAN”,“child”:“toronto”);

Update pricing_qa_1 Use keys ‘qqcmstr’
Set profiles= ARRAY Case when v.parent=‘USA’ And v.child=‘NY1’ Then (“parent”:“USA”,“child”:“NY2”)
When 'ARRAY_APPEND(‘profile’,{“parent”=“CAN”,“child”:“Toronto”) for v in profiles END

But no luck.Any suggestion

2 
UPDATE pricing_qa_1 USE KEYS "qqcmstr"
SET profiles = CASE WHEN { "parent": "USA", "child": "UA1"} IN profiles
                    THEN ARRAY CASE WHEN v == { "parent": "USA", "child": "UA1"}
                                    THEN { "parent": "USA", "child": "UA2"}
                                    ELSE v END
                         FOR v IN profiles END
                    ELSE ARRAY_APPEND(profiles, { "parent": "USA", "child": "UA1"})
               END;

OR

UPDATE pricing_qa_1 USE KEYS "qqcmstr"
SET profiles = CASE WHEN ANY sv IN profiles SATISFIES sv.parent = "USA" AND sv.child = "UA1" END
                    THEN ARRAY CASE WHEN v.parent = "USA" AND v.child = "UA1"
                                    THEN { "parent": "USA", "child": "UA2"}
                                    ELSE v END
                         FOR v IN profiles END
                    ELSE ARRAY_APPEND(profiles, { "parent": "USA", "child": "UA1"})
               END;