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')
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')