Javascript – Not able to Parse a valid javascript Json object with \” to snowflake table using parse_json [duplicate]

Problem:

I have the below json object as a json string

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

I’m trying to insert this string to a variant type column in snowflake table using parse_json but getting this error

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

Below is my SQL query I’m running in Snowflake.

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);

The query is being formed using javascript. I know the reason why it is failing but I’m not sure how to fix this. “Kethan “Ch””. How can I store this string in snowflake without replacing the escape characters ?

Solution:

The backslash \ itself is an escape token for Snowflake’s SQL dialect; as such you’ll need to “double escape” backslashes appearing in your JSON:

"value": "Kethan \\"Ch\\""