How to pass a variant value to an insert statement in a Snowflake stored procedure

Question

My goal: allow the user to pass a JSON string to a Snowflake stored procedure. I then want the stored procedure to cast the string as a variant data type and insert the value into a field in a table that is of the variant data type.

What I have tried:

• wrapping the user's value in parse_json(),to_variant() and cast ( val as VARIANT)
• I have tried all the above functions using the binding method and by constructing a sql statement using a combination of strings and variables with no success

My code is below. Any help you can provide me with would be greatly appreciated, thanks!

CREATE OR REPLACE PROCEDURE LOGGING_TEST_PR (ETL_NAME VARCHAR(16777216),ETL_RUN_GUID VARCHAR(16777216),TASK_NAME VARCHAR(16777216),RECORDS FLOAT8,RUN_DATA_JSON VARCHAR(16777216) )
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//USER INPUT VARIABLES
var USER_JOB_NAME = ETL_NAME.toLowerCase(),USER_JOB_RUN_GUID = ETL_RUN_GUID, USER_JOB_TASK_NAME = TASK_NAME.toLowerCase(),USER_JOB_RECORDS, USER_JOB_RUN_DATA_JSON;

//SQL_STATEMENT_COUNT VARIABLES
var SQL_STATEMENT_COUNT,SQL_COUNT,RECORD_COUNT;

//SQL_STATEMENT_INSERT VARIABLES
var SQL_INSERT_RECORD, SQL_INSERT, ROW_NUM_INSERT, RESULT_INSERT_RETVALUE;

//SQL_STATEMENT_SELECT VARIABLES

//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


//REVERT UNDEFNED VARIABLE VALUES BACK TO NULL
function NULL_PARAM(PARAM)
    {
    if (typeof PARAM === 'undefined')
        {
        return null;
        }
    else 
        {
        return PARAM;
        }
    }

USER_JOB_RECORDS = NULL_PARAM(RECORDS);
USER_JOB_RUN_DATA_JSON = NULL_PARAM(RUN_DATA_JSON);



//SQL STATEMENT VARIABLES
var SQL_STATEMENT_COUNT = " SELECT COUNT(*) AS COUNT FROM EDW_DEV.LOGGING.JOB_LOG_SPROC WHERE LOWER(ETL_NAME) ='" 
+ USER_JOB_NAME +"'"
+ " AND LOWER(ETL_RUN_GUID) ='" + USER_JOB_RUN_GUID +"'"
+ " AND LOWER(TASK_NAME) ='" + USER_JOB_TASK_NAME + "'"; 

var SQL_STATEMENT_SELECT = "SELECT LOWER(ETL_NAME) AS ETL_NAME,LOWER(ETL_RUN_GUID) AS ETL_RUN_GUID,LOWER(TASK_NAME) AS TASK_NAME,START_DTS,END_DTS FROM EDW_DEV.LOGGING.JOB_LOG_SPROC WHERE LOWER(ETL_NAME) ='" 
+ USER_JOB_NAME +"'"
+ " AND LOWER(ETL_RUN_GUID) ='" + USER_JOB_RUN_GUID +"'"
+ " AND LOWER(TASK_NAME) ='" + USER_JOB_TASK_NAME + "'"; 

var SQL_INSERT_RECORD = "INSERT INTO EDW_DEV.LOGGING.JOB_LOG_SPROC (ETL_NAME,ETL_RUN_GUID,TASK_NAME,START_DTS,RECORDS_START,RUN_DATA_JSON)"
+ "VALUES("+ "'" + USER_JOB_NAME + "'," +  "'" + USER_JOB_RUN_GUID + "'," + "'" + USER_JOB_TASK_NAME + "'," + "CONVERT_TIMEZONE('UTC', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_TZ(9)))" + "," 
+ USER_JOB_RECORDS + "," + USER_JOB_RUN_DATA_JSON + ")";

try {
//CHECK IF THE RECORD FOR THE TASK_NAME EXISTS IN THE TABLE
SQL_COUNT = snowflake.createStatement({sqlText:SQL_STATEMENT_COUNT});
RESULT_COUNT = SQL_COUNT.execute(); //EXECUTE MAH SQL STATEMENT
RESULT_COUNT.next(); //GO TO FIRST ROW IN RESULT SET
RECORD_COUNT = RESULT_COUNT.getColumnValue(1);



//THE TASK_NAME IS NOT IN THE TABLE
if (RECORD_COUNT == 0)
   {SQL_INSERT = snowflake.createStatement({sqlText:"INSERT INTO EDW_DEV.LOGGING.JOB_LOG_SPROC (ETL_NAME,ETL_RUN_GUID,TASK_NAME,RECORDS_START,RUN_DATA_JSON) VALUES(?,?,?,?,?);",
   binds:[USER_JOB_NAME,USER_JOB_RUN_GUID,USER_JOB_TASK_NAME,USER_JOB_RECORDS,CAST(USER_JOB_RUN_DATA_JSON AS VARIANT)]});
   INSERT_RESULT = SQL_INSERT.execute();
   ROW_NUM_INSERT = INSERT_RESULT.next();
   return RESULT_INSERT_RETVALUE = INSERT_RESULT.getColumnValue(1);
  }
//THE TASK NAME IS IN THE TABLE 
else if (RECORD_COUNT == 1)
  { return "UPDATE RECORD WITH END TIMESTAMP AND OPTIONAL COUNTS AND RUN DATA JSON"; } 
//RUH ROOOOOOO!
else 
{ return "THERE WAS AN UNFORSEEN ERROR.";}

}
catch (ERR) {
  return ERR
}
$$
CALL LOGGING_TEST_PR('WAYNE','15','BAGELS',1,'{"dude":"whoa"}') }

Answer 1

The PARSE_JSON function is strict about the JSON. If it encounters anything in the string that doesn't match the specs perfectly it will generate an error. You can test the JSON string at https://jsonlint.com.

Answer 2

from a Sales Engineer at Snowflake:

 //create the table for inserting data
create table tester (json variant);


//adding a select statement with the parse_json I am able to insert values into the table in json format
insert into tester
    select parse_json(column1) as v
    from values ('{ "x" : "abc", "y" : false, "z": 10} ') 
       as vals;

//see the result
Select * from tester;