Snowflake数组数据提取失败：无双引号数据处理方案求助

标准JSON要求对象的键和字符串值必须用双引号包裹，你当前的数据用单引号，导致parse_json无法解析。可以通过字符串替换/正则替换将非标准格式转为标准JSON，再进行解析和提取。

方法1：简单全局替换（适用于值内部无单引号的场景） #

如果你的数据中字符串值本身不包含单引号，直接把所有单引号替换成双引号即可：

WITH fake_data AS (
    SELECT parse_json(REPLACE(column1, '''', '"')) as json
    FROM VALUES 
       ('[
     {
     ''type'': ''Custom Interaction'', ''nb choice'': '''', ''responseIdentifier'': ''RESPONSE'', ''BR_identifier'': '''', ''BR_label'': ''''
     },
     {
     ''type'': ''Custom Interaction'', ''nb choice'': '''', ''responseIdentifier'': ''RESPONSE_1'', ''BR_identifier'': '''', ''BR_label'': ''''
     }
     ]'
    )
 )
 select 
    f.value:responseIdentifier::STRING as "responseIdentifier" 
 from fake_data d, 
 TABLE(FLATTEN(input => d.json)) f;

• REPLACE(column1, '''', '"')：将字符串中所有单引号（Snowflake中用两个单引号转义一个）替换成双引号，转为标准JSON格式。
• 之后用parse_json解析，再通过FLATTEN展开数组提取目标数据。

方法2：精准正则替换（适用于键含空格或值可能有特殊情况的场景） #

如果数据中的键包含空格（比如你的nb choice），或者需要更精准区分键和值的单引号，可以用正则分两步替换：

WITH fake_data AS (
    SELECT parse_json(
        REGEXP_REPLACE(
            REGEXP_REPLACE(column1, '''([^'']+)''\s*:', '"\1":'), -- 替换键的单引号为双引号
            '''([^'']*)''', '"\1"' -- 替换值的单引号为双引号
        )
    ) as json
    FROM VALUES 
       ('[
     {
     ''type'': ''Custom Interaction'', ''nb choice'': '''', ''responseIdentifier'': ''RESPONSE'', ''BR_identifier'': '''', ''BR_label'': ''''
     },
     {
     ''type'': ''Custom Interaction'', ''nb choice'': '''', ''responseIdentifier'': ''RESPONSE_1'', ''BR_identifier'': '''', ''BR_label'': ''''
     }
     ]'
    )
 )
 select 
    f.value:responseIdentifier::STRING as "responseIdentifier" 
 from fake_data d, 
 TABLE(FLATTEN(input => d.json)) f;

• 第一步正则'''([^'']+)''\s*:'：匹配'键名':格式的内容，将包裹键的单引号替换成双引号，保留键名本身。
• 第二步正则'''([^'']*)'''：匹配'值'格式的内容，替换为双引号包裹的标准JSON值。

局限性说明 #

如果你的数据中字符串值本身包含单引号（比如'I'm a test value'），直接替换会破坏JSON格式。这种情况需要先对值内部的单引号进行转义（替换为\'），再进行双引号替换，但由于原始数据没有明确的转义标记，处理难度会大幅提升，建议尽量从数据源端生成标准JSON格式。

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