How to extract JSON values within snowflake stored procedure

Question

Within a snowflake sql stored procedure, I have a JSON object set to a variable called json_object:

{"level":1,"object":"OBJECT1","object_schema":"SCHEMA1","object_type":"TABLE"}, 
{"level":2,"object":"OBJECT2","object_schema":"SCHEMA2","object_type":"TABLE"}, 
{"level":3,"object":"OBJECT3","object_schema":"SCHEMA3","object_type":"TABLE"}

and within the procedure, I want to extract each key value (level, object, object_schema, object_type) and put those values into a variable. I'm having trouble with it, I'm not sure if I'm supposed to put it into a table or not. I tried doing

extract_json :=  'SELECT level[0] from '||: json_object ||';';

but I'm sure that's definitely wrong so what are the right ways to extract JSON keys and their values within a snowflake stored procedure? Thanks!

Answer 1

you have to use dynamic sql, below is starting point for you, where if your input is not wrapped in square brakets ad them and then flatten so you'll get one row for each json record. then simpy run loop over it.

begin
let jsonstring varchar := '[{"level":1,"object":"OBJECT1","object_schema":"SCHEMA1","object_type":"TABLE"}, 
{"level":2,"object":"OBJECT2","object_schema":"SCHEMA2","object_type":"TABLE"}, 
{"level":3,"object":"OBJECT3","object_schema":"SCHEMA3","object_type":"TABLE"}]';

let sql varchar := 'select value:"level"::varchar level
    ,value:"object"::varchar object
    ,value:"object_schema"::varchar object_schema
    ,value:"object_type"::varchar object_type
    from (
        select parse_json(''' || jsonstring || ''') val
    ) n
    ,lateral flatten(n.val);';
    let res resultset := (execute immediate :sql);
    let c1 CURSOR FOR res;

    let lev varchar;
    let ob varchar;
    let ob_sc varchar;
    let ob_tp varchar;
    for i in c1
    do 
     lev:= i.level;
     ob:= i.object;
     ob_sc:= i.object_schema;
     ob_tp:= i.object_type;

    // Do what you want to do with values
    end for;
    
    return table(res);

end
;