Requirement : To update the Array Column for all those which is having one of the key value as empty
details_array in table looks like below
DETAILS_ARRAY
[
{
"dl_country": "",
"typeid": "U"
}
]
Tried as below statement to update the key value but in vain
MERGE INTO tgt
USING (
SELECT a_id, d_id, dl_country
FROM doc_fix
WHERE a_id = 12321 AND d_id = 88899
) src
ON tgt.a_id = src.a_id AND tgt.d_id = src.d_id
WHEN MATCHED THEN
UPDATE SET
DETAILS_ARRAY = OBJECT_INSERT(tgt.DETAILS_ARRAY,'dl_country', src.dl_country)
;
Error:
Invalid argument types for function 'OBJECT_INSERT': (ARRAY, VARCHAR(21), VARCHAR(16777216), BOOLEAN)
Above is the example for one id , but i need to update all id’s having empty string in the dl_country field