Double Quotes Parsing Error Expression

[nosingh]

I am getting the below error when I am using the expression to run using the filter. If I remove them and run it, it works fine. The double quotes are causing issues. Any help would be greatly appreciated? I have changed the filed names because of confidentiality issues. Thank You

ERROR:
Expression parsing failed near – amap,'" >> { << ‘, ‘{’),’ (Reason: Mismatched input ‘{’ at line 9:60. Expecting one of: {, ‘||’, ‘&&’, ‘^’, ‘==’, ‘!=’, ‘>’, ‘<’, ‘>=’, ‘<=’, ‘+’, ‘-’, ‘*’, ‘/’, ‘%’, ‘?’, ‘instanceof’, ‘in’, ‘matches’, ‘[’, ‘(’, ‘.’, PropertyRef}; Resolution: Please check expression syntax)

SQL:
“REPLACE INTO abc.stg_order
select DISTINCT 1 as orderno
, 1 as orderId , 1 as subOrderId, 1 as itemId,
case when pi.amap is null then
JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.amap,'”{‘, ‘{’),’}“‘,’}‘),’\”‘,’“‘),‘country’)
else
REPLACE(pi.amap::country, ‘"’, ‘’)
end as country
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,’”{‘, ‘{’),’}“‘, ‘}’),’\”‘, ‘"’) ,‘ff’)
else REPLACE(pi.amap::ff, ‘"’, ‘’)
end as ff
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,’“{‘, ‘{’),’}”‘, ‘}’),’\“', '”‘) ,‘frame’)
else REPLACE(pi.amap::frame, ‘"’, ‘’)
end as frame
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,’“{‘, ‘{’),’}”‘, ‘}’),’\“', '”‘) ,‘orientation’)
else REPLACE(pi.amap::orientation, ‘"’, ‘’)
end as orientation
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,’" {‘, ‘{’),’}“‘, ‘}’),’\”‘, ‘"’) ,‘side’)
else REPLACE(pi.amap::side, ‘"’, ‘’)
end as side
, case when pi.amap is null then REPLACE(REPLACE(REPLACE(pi.att ,’“{‘, ‘{’),’}”‘, ‘}’),’\“‘,’”')
else pi.amap
end as amap
,ci.componentId
, pi.ingest_ts
from
abc.product pi
JOIN abc.component ci on
pi.Id = ci.Id
WHERE pi.ingest_ts > ‘“+_filter_1+”’"

[del]

Hi @nosingh,

2 tips:

1. The community forum reformatting can be a pain, so when you post code, it’s easiest to use the preformatted text icon so that your code doesn’t get reformatted and difficult to troubleshoot.
2. To resolve the error in your current expression, you need to escape (with a backslash) both your double-quotes and the backslash character:

"REPLACE INTO abc.stg_order
select DISTINCT 1 as orderno
, 1 as orderId , 1 as subOrderId, 1 as itemId,
case when pi.amap is null then
JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.amap,'\"{', '{'),'}\"','}'),'\\\"','\"'),'country')
else
REPLACE(pi.amap::country, '\"', '')
end as country
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'ff')
else REPLACE(pi.amap::ff, '\"', '')
end as ff
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'frame')
else REPLACE(pi.amap::frame, '\"', '')
end as frame
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'orientation')
else REPLACE(pi.amap::orientation, '\"', '')
end as orientation
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,'\" {', '{'),'}\"', '}'),'\\\"', '\"') ,'side')
else REPLACE(pi.amap::side, '\"', '')
end as side
, case when pi.amap is null then REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"','\"')
else pi.amap
end as amap
,ci.componentId
, pi.ingest_ts
from
abc.product pi
JOIN abc.component ci on
pi.Id = ci.Id
WHERE pi.ingest_ts > '"+_filter_1+"'"