How do you pass a JSON object through a snowflake stored proc then loop through the key values

Question

I'm trying to pass a JSON object containing fully qualified tables into a stored procedure so I can loop through it and put the key values into a table.

CREATE OR REPLACE PROCEDURE proc_db.proc_schema.json_test(
    tables VARIANT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    key_value_pairs VARIANT;
    keys_array ARRAY;
    key_value OBJECT;
    key STRING;
    database_name VARCHAR;
    table_for_tables VARCHAR;
    sql_command VARCHAR;
BEGIN

## parsing through the JSON object but commented out since it will parse in the FOR loop anyway
-- sql_command := (SELECT PARSE_JSON(:tables));

## create a table to put the extracted key values into
    table_for_tables := 'CREATE OR REPLACE TEMP TABLE proc_db.proc_schema.JSON_OBJECTS (DATABASE_NAME VARCHAR)';
    EXECUTE IMMEDIATE :table_for_tables;

## loop through the JSON object and put the extracted database_name into the DATABASE_NAME column in the table
    FOR key_value IN (SELECT PARSE_JSON(:tables))
    DO

## split the key value in the JSON from the first full stop to get database_name
        database_name := SPLIT_PART(:key_value, '.', 1);

## insert into the table
        sql_command := 'INSERT INTO proc_db.proc_schema.JSON_KEY_VALUES (DATABASE_NAME) VALUES('||:database_name||');';
        EXECUTE IMMEDIATE :sql_command;
        
  END FOR;
      
      RETURN 'Success!';
END;
$$;

This is the call block:

call proc_db.proc_schema.json_test(
      PARSE_JSON('{
        "tables": ["DB_PROD_1.DB_PROD_SCHEMA.DB_PROD_TABLE","DB_PROD.MY_SCHEMA.MY_TABLE", "DB_DEV.MY_SCHEMA.MY_TABLE_2"]
      }')
);

The result I want is to have a table with DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME in a table with each object_type underneath the correct column, e.g DB_PROD_1, DB_PROD, DB_DEV under DATABASE_NAME etc.

And I've gotten an error 'Executing NULL statement is not permitted.'. I think it may have something to do with the SPLIT_PART part because when I set the database_name variable outside of the FOR loop and tried to return it,

    database_name := SPLIT_PART(:key_value, '.', 1);
    RETURN :database_name;

it says 'Invalid argument types for function 'SPLIT_PART': (OBJECT, VARCHAR(1), NUMBER(1,0))'. Not sure where to go from here. Any help is appreciated, thanks!

Answer 1

You need to update code for

1. above script misses lot's of snowscripting syntaxes.
2. Table created and insert are not matching.

Below is script to start with

CREATE OR REPLACE PROCEDURE json_test(
    tables VARIANT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    key_value_pairs VARIANT;
    keys_array ARRAY;
    key_value OBJECT;
    key STRING;
    database_name VARCHAR;
    table_for_tables VARCHAR;
    sql_command VARCHAR;
BEGIN


-- parsing through the JSON object but commented out since it will parse in the FOR loop anyway
-- sql_command := (SELECT PARSE_JSON(:tables));

--# create a table to put the extracted key values into
    table_for_tables := 'CREATE OR REPLACE TEMP TABLE JSON_OBJECTS (DATABASE_NAME VARCHAR)';
    EXECUTE IMMEDIATE :table_for_tables;


    let query := '
    select value::varchar key from table (flatten(PARSE_JSON('''||tables||'''):tables::array)
      )';
    let rs resultset := (execute immediate :query);
    
    let   c1 CURSOR FOR rs;

-- loop through the JSON object and put the extracted database_name into the DATABASE_NAME column in the table
    FOR key_value IN  c1
    DO

    key := key_value.key;
-- split the key value in the JSON from the first full stop to get database_name
          select SPLIT_PART(:key, '.', 1) into database_name;
    
-- insert into the table
        sql_command := 'INSERT INTO JSON_OBJECTS (DATABASE_NAME) VALUES('''||:database_name||''');';
        EXECUTE IMMEDIATE :sql_command;
        
  END FOR;
      
      RETURN 'Success!';
END;
$$;

call json_test(PARSE_JSON('{
            "tables": ["DB_PROD_1.DB_PROD_SCHEMA.DB_PROD_TABLE","DB_PROD.MY_SCHEMA.MY_TABLE", "DB_DEV.MY_SCHEMA.MY_TABLE_2"]
          }'));


select * from JSON_OBJECTS;