将JSON数据加载至结构化表时遇STATEMENT_ERROR错误求助

错误原因 #

报错Expression in variant for loop does not evaluate to an array or an object的核心原因有两点：

1. 数组循环语法错误：原代码中FOR i IN ARRAY_SIZE(fiscal_year) - 1 DO的写法不符合Snowflake SQL存储过程的循环规范，该写法将单个数值当作迭代范围，而非合法的数组或索引区间。
2. 缺少字段合法性校验：未判断JSON中Fiscal_Year、Gross_profit、Net_income字段是否为数组类型，若字段值为非数组（如null、单个值），会直接触发异常。

修复后的代码 #

CREATE OR REPLACE PROCEDURE parse_json_and_load_output()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS CALLER
AS
$$
DECLARE
    input_json VARIANT;
    company_name STRING;
    fiscal_year ARRAY;
    gross_profit ARRAY;
    net_income ARRAY;
    fiscal_year_item VARIANT;
    gross_profit_item VARIANT;
    net_income_item VARIANT;
    counter int;
    result STRING;

    input_cursor CURSOR FOR
        SELECT json_content
        FROM temp_table;

BEGIN
    FOR var IN input_cursor DO
        input_json := var.json_content;

        company_name := input_json:company_name[0].value::STRING;
        -- 强制将字段转为数组，避免非数组值导致异常
        fiscal_year := IFF(ARRAY_SIZE(input_json:Fiscal_Year) IS NOT NULL, input_json:Fiscal_Year, []);
        gross_profit := IFF(ARRAY_SIZE(input_json:Gross_profit) IS NOT NULL, input_json:Gross_profit, []);
        net_income := IFF(ARRAY_SIZE(input_json:Net_income) IS NOT NULL, input_json:Net_income, []);
        counter:=0;

        -- 修正数组索引循环的正确语法
        FOR i IN 0 TO ARRAY_SIZE(fiscal_year) - 1 DO
             -- 校验索引是否在三个数组的有效范围内，避免越界
             IF i < ARRAY_SIZE(gross_profit) AND i < ARRAY_SIZE(net_income) THEN
                fiscal_year_item := fiscal_year[i];
                gross_profit_item := gross_profit[i];
                net_income_item := net_income[i];

                INSERT INTO tgt_temp (company_name, fiscal_year, gross_Profit, net_income)
                VALUES (
                    company_name,
                    fiscal_year_item.value::STRING,
                    gross_profit_item.value::STRING,
                    net_income_item.value::STRING
                );
                counter:=counter+1;
             END IF;
        END FOR;
    END FOR;

    RETURN 'Data loaded successfully into output_table.';
END;
$$;

关键修复说明 #

• 修正循环语法：改用FOR i IN 0 TO ARRAY_SIZE(fiscal_year) - 1 DO，这是Snowflake SQL存储过程中遍历数组索引的标准写法。
• 字段合法性校验：通过IFF函数判断字段是否为数组，若不是则赋值为空数组，避免循环时触发类型异常。
• 索引越界防护：增加数组长度判断，确保当前索引同时存在于三个目标数组中，防止因数组长度不一致导致的索引越界错误。

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