I am trying to load the data from json to structured file and getting below error :

092234 (P0000): Uncaught exception of type 'STATEMENT_ERROR' on line 35 at position 8 : Expression in variant for loop does not evaluate to an array or an object

Below is the procedure

CREATE OR REPLACE PROCEDURE parse_json_and_load_output()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS CALLER
AS
$$
DECLARE
    -- Variables to hold the JSON data from the input table
    input_json VARIANT;

    -- Variables to hold values for each field
    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;
    -- Temporary variable to hold the result of each row
    result STRING;

    -- Cursor to loop through the input table rows
    input_cursor CURSOR  FOR
        SELECT json_content
        FROM temp_table;

BEGIN
    -- Iterate over each row in the input table
    FOR var IN input_cursor DO
        input_json := var.json_content;

        -- Extract values from the JSON object
        company_name := input_json:company_name[0].value::STRING;
        fiscal_year := input_json:Fiscal_Year;
        gross_profit := input_json:Gross_profit;
        net_income := input_json:Net_income;
counter:=0;

        FOR i IN ARRAY_SIZE(fiscal_year) - 1 DO
        
             fiscal_year_item := fiscal_year[i];
             gross_profit_item := gross_profit[i];
             net_income_item := net_income[i];

            -- Insert the parsed values into the output_table
            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 FOR;
    END FOR;

    -- Return a success message
    RETURN 'Data loaded successfully into output_table.';
END;
$$;

Please help me to fix this.

Thanks much!

I am trying to load the data from json to structured file and getting below error :

092234 (P0000): Uncaught exception of type 'STATEMENT_ERROR' on line 35 at position 8 : Expression in variant for loop does not evaluate to an array or an object

Below is the procedure

CREATE OR REPLACE PROCEDURE parse_json_and_load_output()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS CALLER
AS
$$
DECLARE
    -- Variables to hold the JSON data from the input table
    input_json VARIANT;

    -- Variables to hold values for each field
    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;
    -- Temporary variable to hold the result of each row
    result STRING;

    -- Cursor to loop through the input table rows
    input_cursor CURSOR  FOR
        SELECT json_content
        FROM temp_table;

BEGIN
    -- Iterate over each row in the input table
    FOR var IN input_cursor DO
        input_json := var.json_content;

        -- Extract values from the JSON object
        company_name := input_json:company_name[0].value::STRING;
        fiscal_year := input_json:Fiscal_Year;
        gross_profit := input_json:Gross_profit;
        net_income := input_json:Net_income;
counter:=0;

        FOR i IN ARRAY_SIZE(fiscal_year) - 1 DO
        
             fiscal_year_item := fiscal_year[i];
             gross_profit_item := gross_profit[i];
             net_income_item := net_income[i];

            -- Insert the parsed values into the output_table
            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 FOR;
    END FOR;

    -- Return a success message
    RETURN 'Data loaded successfully into output_table.';
END;
$$;

Please help me to fix this.

Thanks much!

• stored-procedures
• snowflake-cloud-data-platform
• procedure
• snowflake-schema

• Hi - for a start, don't insert row-by-row as the performance will be terrible. Just run INSERT INTO... SELECT FROM ... as a single statement – NickW Commented Mar 28 at 16:04
• Its hard to reproduce your issue without any sample input data SELECT json_content FROM temp_table; You need to share sample sample data for this – samhita Commented Mar 28 at 17:37

1 Answer 1

I tried to reproduce your issue with sample data

There are a couple of points to note here -

1.In the For loop

FOR i IN ARRAY_SIZE(fiscal_year) - 1 DO

If I understand correctly above is a Counter based FOR loop, so you need to give a start point as mentioned in documentation

FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }
  <statement>;
  [ <statement>; ... ]
END { FOR | LOOP } [ <label> ] ;

so above statement becomes

FOR i IN 0 TO ARRAY_SIZE(fiscal_year) - 1 DO  --I have started with 0 but you can use any start point 

Once you fix above you are going to encounter further issues as I highlighted below -

2.In the INSERT statement since you are using variables to insert , it should be :fiscal_year_item instead of fiscal_year_item , note the colon (:).

Here is a sample json I used

{
  "Fiscal_Year": [
    2020,
    2021
  ],
  "Gross_profit": [
    50000,
    60000
  ],
  "Net_income": [
    30000,
    40000
  ],
  "company_name": "A"
}

Here is the procedure as per above data(Tables are in Test.test schema, you can change accordingly)

CREATE OR REPLACE PROCEDURE parse_json_and_load_output()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS CALLER
AS
$$
DECLARE
    -- Variables to hold the JSON data from the input table
    input_json VARIANT;

    -- Variables to hold values for each field
    company_name STRING;
    fiscal_year ARRAY;
    gross_profit ARRAY;
    net_income ARRAY;
    fiscal_year_item STRING;
    gross_profit_item STRING;
    net_income_item STRING;
counter int;
    -- Temporary variable to hold the result of each row
    result STRING;

    -- Cursor to loop through the input table rows
    input_cursor CURSOR  FOR
        SELECT json_content
        FROM test.test.temp_table;

BEGIN
    -- Iterate over each row in the input table
    FOR var IN input_cursor DO
        input_json := var.json_content;
        

        -- Extract values from the JSON object
        company_name := input_json:company_name::STRING;

        fiscal_year := input_json:Fiscal_Year;
        gross_profit := input_json:Gross_profit;
        net_income := input_json:Net_income;
counter:=0;

        FOR i IN 0 to ARRAY_SIZE(fiscal_year) - 1 DO
             fiscal_year_item := fiscal_year[i];
             gross_profit_item := gross_profit[i];
             net_income_item := net_income[i];

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

    -- Return a success message
    RETURN 'Data loaded successfully into output_table.';
END;
$$;

Upon calling the procedure

call parse_json_and_load_output() ;

Another point is you are not using counter anywhere, do you need it?