[SOLVED] Update Object_construct nested in an Array

Update Object_construct nested in an Array_construct in Snowflake

Can anyone please help me with this scenario where I have might have multiple OBJECT_CONSTRUCT nested within an ARRAY_CONSTRUCT. I am not able to update one value of an element within it. I am using Snowflake. Here is an example of data,

MyDict
[
  {
    key1 : abc
    key2 : 123
    key3 : efg456
  }
]

Now if I want to change value of key2 which is 123, I am not able to. I have tried OBJECT_INSERT(MyDict::variant,'key2', 888, TRUE) and it threw casting error. Also tried ARRAY_PREPEND and ARRAY_INSERT, none of them worked.

Solution

You can first delete the key and then insert based on whether or not key2 matches the value

OBJECT_INSERT(OBJECT_DELETE(value, 'key2'), 'key2', 'test_value')

EDIT : To update

merge into test as target
using (
  select dict,
    array_agg(
      iff(
        value:key2::string = '1234',
        object_insert(object_delete(value, 'key2'), 'key2', 'test_value1'),
        value
      )
    ) as updated_dict
  from test,
  lateral flatten(input => dict)
  group by dict
) as source
on target.dict = source.dict
when matched then
  update set dict = source.updated_dict;

Sample data

Outputs after merge