I have to write a JavaScript UDF function in Snowflake that receives a variant as the argument and do some value changes for a few keys. All is good but there's one particular attribute (PROPERTY) that looks like a nested JSON but it has some have escapes (backslash) which add some complexity because one of the requirements is to transform the value for "PROP_3" (nested attribute within PROPERTY). See screen shot. Any ideas how to approach this request taking the screen shot as the input for the UDF?
I tend to avoid UDFs when straight SQL approaches are available. There are rare times when procedural code can outperform straight SQL by doing less work, but that's uncommon.
create or replace temp table t1 as select
parse_json($$ {"KEY":"ABCD","PROPERTY":"{\"PROP1\":\"VALUE1\",\"PROP2\":\"VALUE2\",\"PROP3\":\"VALUE3\",\"PROP4\":null}","ATTR_1":0,"ATTR_2":"customer"} $$) as V;
select
object_insert(V, 'PROPERTY', object_insert(parse_json(V:PROPERTY), 'PROP3', 'NEW_VALUE', true), true) as JSON
from t1;
If you need to convert PROP3 back to a string property, you can create a very simple JavaScript UDF that applies the JSON.stringify() method.