使用PARSE_JSON将含转义双引号的JSON字符串导入Snowflake Variant列失败的问题求助

问题描述 #

我尝试把一段包含转义双引号的JSON字符串通过PARSE_JSON函数插入到Snowflake的Variant类型列中，但一直收到报错：

Error parsing JSON: missing comma, line 6, pos 30

我的JSON字符串如下：

{
"type": "employee",
"details": [
{
"key": "name",
"value": "Kethan \"Ch\""
}
],
"mobile": "9999999999"
}

执行的MERGE SQL语句如下：

MERGE INTO PLAY_GROUND.SAMPLE_TABLES.EMPLOYEES AS target
USING (
SELECT
'1234'::VARIANT AS EMP_ID,
'2023-10-19T09:01:42.387Z'::VARIANT AS LAST_MODIFIED,
PARSE_JSON('{
"type": "employee",
"details": [
{
"key": "name",
"value": "Kethan \"Ch\""
}
],
"mobile": "9999999999"
}')::VARIANT AS DETAILS,
'india'::VARIANT AS COUNTRY
) AS source
ON target.EMP_ID = source.EMP_ID
WHEN MATCHED THEN
UPDATE SET
target.LAST_MODIFIED = source.LAST_MODIFIED,
target.DETAILS = source.DETAILS,
target.COUNTRY = source.COUNTRY
WHEN NOT MATCHED THEN
INSERT (EMP_ID, LAST_MODIFIED, DETAILS, COUNTRY)
VALUES (source.EMP_ID, source.LAST_MODIFIED, source.DETAILS, source.COUNTRY);

这段SQL是通过JavaScript生成的，我知道问题出在"Kethan \"Ch\""这个转义的双引号上，但不知道怎么在不替换转义字符的前提下解决这个问题。

问题原因 #

核心问题是转义规则的冲突：

1. JSON语法中，要在字符串里表示双引号，需要用\"来转义；
2. 但当你把这段JSON作为字符串参数传给SQL的PARSE_JSON('...')时，SQL会先对字符串里的转义字符进行解析，把\"直接转换成普通的"；
3. 这样传到PARSE_JSON里的JSON就变成了"value": "Kethan "Ch""，这明显违反了JSON的语法规则（字符串里的未转义双引号会提前终止字符串），所以才会报解析错误。

解决方案 #

这里提供两种可靠的解决办法，推荐第二种更安全的方式：

1. 双重转义双引号（适用于直接拼接SQL字符串的场景）

在JavaScript生成SQL的时候，把JSON里的每个\"替换成\\\"，也就是做一次额外的转义。这样当SQL解析字符串时，会把\\\"转换成\"，刚好符合JSON的转义要求。

修改后的PARSE_JSON部分应该是这样：

PARSE_JSON('{
"type": "employee",
"details": [
{
"key": "name",
"value": "Kethan \\\"Ch\\\""
}
],
"mobile": "9999999999"
}')::VARIANT AS DETAILS

2. 使用绑定变量（推荐，更安全且无需手动转义）

不要把JSON字符串直接拼进SQL语句里，而是用Snowflake支持的绑定变量来传递参数。这样JavaScript会把原始的JSON字符串直接传给Snowflake，跳过SQL层面的转义解析，完全避免转义冲突的问题。

举个JavaScript（用snowflake-sdk）的示例：

// 原始的JSON字符串，不需要额外转义
const rawJson = `{
"type": "employee",
"details": [
{
"key": "name",
"value": "Kethan \"Ch\""
}
],
"mobile": "9999999999"
}`;

// 定义SQL模板，用:details作为绑定变量
const sqlTemplate = `MERGE INTO PLAY_GROUND.SAMPLE_TABLES.EMPLOYEES AS target
USING (
SELECT
'1234'::VARIANT AS EMP_ID,
'2023-10-19T09:01:42.387Z'::VARIANT AS LAST_MODIFIED,
PARSE_JSON(:details)::VARIANT AS DETAILS,
'india'::VARIANT AS COUNTRY
) AS source
ON target.EMP_ID = source.EMP_ID
WHEN MATCHED THEN
UPDATE SET
target.LAST_MODIFIED = source.LAST_MODIFIED,
target.DETAILS = source.DETAILS,
target.COUNTRY = source.COUNTRY
WHEN NOT MATCHED THEN
INSERT (EMP_ID, LAST_MODIFIED, DETAILS, COUNTRY)
VALUES (source.EMP_ID, source.LAST_MODIFIED, source.DETAILS, source.COUNTRY);`;

// 执行SQL时传入绑定变量
conn.execute({
  sqlText: sqlTemplate,
  binds: { details: rawJson },
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('执行出错:', err);
    } else {
      console.log('操作成功');
    }
  }
});

总结 #

• 双重转义可以快速解决单个转义字符的问题，但如果JSON里有更多特殊字符（比如反斜杠、换行符等），手动转义容易出错；
• 绑定变量是更优的方案，不仅能避免转义问题，还能防止SQL注入，代码也更清晰易维护。

备注：内容来源于stack exchange，提问作者ck22