如何在Snowflake存储过程中提取JSON键值并赋值给变量

首先，你需要先把当前的json_object变量内容转换成合法的JSON数组——也就是把多个JSON对象用[]包裹起来，变成：

[{"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"}]

Snowflake的JSON函数仅能识别标准JSON格式，未包裹的多个JSON对象无法被正确解析。

接下来分两种场景处理：

场景1：不存入表，直接在存储过程中提取 #

可以使用FLATTEN函数展开JSON数组，再用箭头运算符:提取每个键的值，结合存储过程的结果集遍历逻辑处理每一条数据：

CREATE OR REPLACE PROCEDURE EXTRACT_JSON_DATA()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    json_object 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"}]';
    res RESULTSET;
    rec RECORD;
    v_level INTEGER;
    v_object VARCHAR;
    v_object_schema VARCHAR;
    v_object_type VARCHAR;
BEGIN
    -- 展开JSON数组并提取字段
    res := (SELECT 
                VALUE:level::INTEGER AS level,
                VALUE:object::VARCHAR AS object,
                VALUE:object_schema::VARCHAR AS object_schema,
                VALUE:object_type::VARCHAR AS object_type
            FROM TABLE(FLATTEN(INPUT => PARSE_JSON(:json_object))));
    
    -- 遍历结果集，将每个字段赋值给变量（示例逻辑）
    FOR rec IN res DO
        v_level := rec.level;
        v_object := rec.object;
        v_object_schema := rec.object_schema;
        v_object_type := rec.object_type;
        
        -- 这里可以添加你的业务逻辑，比如打印或进一步处理
        CALL SYSTEM$LOG('提取到：level=' || v_level || ', object=' || v_object);
    END FOR;
    
    RETURN '处理完成';
END;
$$;

关键说明：

• PARSE_JSON(:json_object)：将字符串类型的JSON转换成Snowflake的JSON数据类型
• TABLE(FLATTEN(...))：将JSON数组展开成一行一行的记录
• VALUE:level::INTEGER：通过箭头运算符提取JSON对象的level键，并转换成指定数据类型

场景2：先存入临时表再提取 #

如果需要多次使用这些数据，或者需要更复杂的查询，可以先将JSON数组存入临时表，再进行提取：

CREATE OR REPLACE PROCEDURE EXTRACT_JSON_VIA_TABLE()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    json_object 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"}]';
BEGIN
    -- 创建临时表
    CREATE OR REPLACE TEMP TABLE json_temp (data VARIANT);
    
    -- 插入JSON数组
    INSERT INTO json_temp VALUES (PARSE_JSON(:json_object));
    
    -- 提取字段并处理（这里可以直接查询或赋值给变量）
    FOR rec IN (SELECT 
                    VALUE:level::INTEGER AS level,
                    VALUE:object::VARCHAR AS object,
                    VALUE:object_schema::VARCHAR AS object_schema,
                    VALUE:object_type::VARCHAR AS object_type
                FROM json_temp, TABLE(FLATTEN(INPUT => data))) DO
        -- 业务逻辑处理
        CALL SYSTEM$LOG('从临时表提取：level=' || rec.level || ', object=' || rec.object);
    END FOR;
    
    RETURN '处理完成';
END;
$$;

你之前写法的问题 #

你尝试的extract_json := 'SELECT level[0] from '||: json_object ||';'错误在于：

1. json_object是字符串变量，不是表名，直接拼接会导致SQL语法错误
2. 未将字符串转换成JSON类型，也没有展开数组的逻辑

内容的提问来源于stack exchange，提问作者Justine