sqlarrayssnowflake-cloud-data-platform

Replace certain values of certain objects in array in Snowflake


I have a table in Snowflake with a column type of variant. I would like to replace certain values that belong to certain objects.

Sample Data

create or replace temporary table database.tmp.table1 as (
 select 1 as order_id, 
    '{
      "channel": "phone",
      "result": "approved" 
    }' as details union all
 select 2 as order_id, 
    '{
      "channel": "cell",
      "result": "approved" 
    }' as details union all
 select 3 as order_id, 
    '{
      "channel": "store",
      "result": "phone"
    }' as details
);
order_id details
1 { "channel": "phone", "result": "approved" }
2 { "channel": "cell", "result": "approved" }
3 { "channel": "store", "result": "phone" }

I would like to UPDATE the column and replace "phone" or "cell" with the value of "mobile", but only for the Channel object. I don't want to touch the Result object.

Desired Output:

order_id details
1 { "channel": "mobile", "result": "approved" }
2 { "channel": "mobile", "result": "approved" }
3 { "channel": "store", "result": "phone" }

Would this need a UDF or do I need to flatten the array, make my replacements, and then put it back together? The array is actually quite long with many other objects too, so would prefer to avoid deconstructing and reconstructing the array.

I've been trying to find something similar to below but for the paired value rather than the object name. This is how I would change "channel" to "whatever", but not sure if this could modified to change the paired value of that object.

update database.tmp.table1 
  set details = object_delete(object_insert(details, 'whatever', details:channel), 'channel')

Solution

  • object_insert has an extra optional parameter "updateFlag". See docs

    update database.tmp.table1 
      set details = object_insert(details,
                       'channel', 
                       iff(details:channel in ('phone', 'cell'), 'mobile', details:channel),
                       true)
    

    minor sidenote, your sample code has "details" as a string instead of variant so this code doesn't work, unless it's run with parse_json.

    minor sidenote #2. just swapping the order of delete and insert in your test would have the almost the same effect, except that any record that didn't already have the attribute would have it added.

    object_insert(object_delete(details,'channel'),'channel','new_value')