Snowflake存储过程接收JSON参数插入目标表的实现求助

需求说明 #

• 实现Snowflake存储过程，接收JSON格式参数，将数据插入目标表user_json_feedback
• JSON数据包含User、EntityID、Entity Type三个核心字段
• 目标表包含User、ID、Entity Type、Region、Date五列：
  • Region默认值为"NA"
  • Date取当前日期
• 插入成功返回true，失败返回false

原代码存在的问题 #

• 返回值类型不匹配：声明返回BOOLEAN，但代码返回字符串
• 获取当前日期的方式冗余，无需单独执行语句
• 错误解析VARIANT参数：Snowflake的VARIANT类型无需用JSON.parse()处理
• SQL语句中直接引用JS变量的方式错误，会导致语法报错
• 未处理JSON数组：传入多条数据的数组时无法正确展开插入
• 字段名引用错误：目标表中带空格的字段Entity Type未正确包裹

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE SP_UPDATE_JSON_DATA(JSON_DATA VARIANT)
RETURNS BOOLEAN 
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS 
$$   
try {
    // 定义插入SQL，使用LATERAL FLATTEN展开JSON数组，绑定变量传递REGION
    const sqlCommand = `
        INSERT INTO user_json_feedback ("User", "ID", "Entity Type", "Region", "Date")
        SELECT 
            f.value:USER::STRING,
            f.value:ENTITY_ID::STRING,
            f.value:ENTITY_TYPE::STRING,
            ?,
            CURRENT_DATE
        FROM TABLE(FLATTEN(input => ?)) f
    `;
    
    // 执行SQL，绑定REGION和JSON_DATA参数
    snowflake.execute({
        sqlText: sqlCommand,
        binds: ['NA', JSON_DATA]
    });
    
    return true;
} catch (err) {
    // 可选：打印错误信息到日志表便于调试
    snowflake.execute({sqlText: `INSERT INTO error_log (message) VALUES ('${err.message}')`});
    return false;
}
$$;

关键修改说明 #

1. 返回值修正：try块返回true，catch块返回false，严格匹配BOOLEAN返回类型
2. JSON数组处理：使用TABLE(FLATTEN(input => ?))展开传入的JSON数组，支持批量插入多条数据
3. 参数绑定：用?作为占位符传递REGION和JSON_DATA，避免SQL注入，同时保证语法正确
4. 字段类型转换：用::STRING明确转换JSON字段为字符串类型，避免隐式转换问题
5. 简化日期获取：直接在SQL中使用CURRENT_DATE，无需单独执行语句
6. 正确引用字段：带空格的字段名用双引号包裹（"Entity Type"），符合Snowflake语法规范

调用示例 #

CALL SP_UPDATE_JSON_DATA('[
    {"USER":"XYZ","ENTITY_ID":"BMT0001","ENTITY_TYPE":"BMT"},
    {"USER":"ABC","ENTITY_ID":"BMT0002","ENTITY_TYPE":"BMT"}
]');

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