How to manipulate Json nested attributes in a JavaScript UDF in Snowflake

Question

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?

input format for the UDF argument Thanks

Answer 1

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.