含Mathematical Markup Language的数据无法在Snowflake中解析查询求助

核心错误点 #

• 非标准JSON格式：你提供的数据是Python字典风格（使用单引号），但Snowflake仅支持严格的JSON语法——所有键和字符串值必须用双引号包裹。
• 过度转义的双引号：HTML内容中的双引号被转成了""（双重转义），这会破坏JSON的结构，导致解析失败。
• 多行字符串语法问题：直接在SQL语句中换行书写字符串常量，容易触发Snowflake的语法校验错误，需要用多行字符串标识符（$$）来处理。

修正后的测试SQL1 #

WITH fake_data AS (
    SELECT parse_json(column1) as json
    FROM VALUES 
    (    
$$
[{"type": "Choice", "nb choice": 4, "responseIdentifier": "RESPONSE", "BR_identifier": "A", "BR_label": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b147eb1d28071779175b6a6b4afae\" alt=\"M07_XX01_10398_G07_A_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_1": "A", "choice_label_1": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b147eb1d28071779175b6a6b4afae\" alt=\"M07_XX01_10398_G07_A_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_2": "B", "choice_label_2": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b1706d6a27736d17112391d143a4d\" alt=\"M07_XX01_10398_G07_B_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_3": "C", "choice_label_3": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b16400aa31403586561c63dc0c123\" alt=\"M07_XX01_10398_G07_C_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_4": "D", "choice_label_4": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b162a71d264684cca5b0f862ceeba\" alt=\"M07_XX01_10398_G07_D_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>"}]
$$
    )
 )
 select 
    to_char(f.value:responseIdentifier) as "responseIdentifier" 
 from fake_data d, table(flatten(input=>d.json)) f;

修正后的测试SQL2 #

WITH fake_data AS (
SELECT
to_json(
parse_json($$
[{"type": "Choice", "nb choice": 4, "responseIdentifier": "RESPONSE", "BR_identifier": "A", "BR_label": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b147eb1d28071779175b6a6b4afae\" alt=\"M07_XX01_10398_G07_A_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_1": "A", "choice_label_1": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b147eb1d28071779175b6a6b4afae\" alt=\"M07_XX01_10398_G07_A_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_2": "B", "choice_label_2": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b1706d6a27736d17112391d143a4d\" alt=\"M07_XX01_10398_G07_B_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_3": "C", "choice_label_3": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b16400aa31403586561c63dc0c123\" alt=\"M07_XX01_10398_G07_C_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>", "choice_identifier_4": "D", "choice_label_4": "<p><img src=\"abcmedia://mediamanager/http_2_www_0_abctesting_0_com_1_ontologies_1_abc-deploy-test-package_0_rdf_3_i60a67b162a71d264684cca5b0f862ceeba\" alt=\"M07_XX01_10398_G07_D_v3.svgz\" width=\"55%\" type=\"image/svg+xml\" class=\"\"></img></p>"}]
$$)
) f
)
select
*
from
fake_data;

关键修复说明 #

1. 将所有单引号替换为双引号，符合JSON标准语法。
2. 把HTML中的""还原为单个转义双引号\"，确保字符串在JSON中被正确解析。
3. 使用$$作为多行字符串分隔符，避免换行导致的语法错误，同时简化转义处理。

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