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\\""