如何让Snowflake的FLATTEN函数返回点符号格式的PATH而非方括号

问题背景 #

在使用Snowflake的FLATTEN函数解析JSON格式的Variant列时，我们需要利用PATH列的值动态构造SELECT子句，采用<variant_col>:<path值>的点符号格式。但当JSON键包含点（如"0.text"）时，FLATTEN返回的PATH值为['0.text']，而普通键（如legal_name）返回直接的键名，导致拼接后的SQL语句无法正确解析带点的键。

解决方案 #

可以通过字符串处理统一PATH的格式，将数组格式的PATH转换为带双引号的点符号格式，具体用CASE结合正则表达式替换实现：

-- 生成可正常执行的动态SELECT语句
select 'select ' || listagg(
    'variant_col:' || 
    case 
        -- 判断PATH是否为数组索引格式
        when path like '[''%' then regexp_replace(path, '^\[''(.+)''\]$', '"\1"')
        else path
    end, 
    ',\n'
) || ' from notation_issue'
from flatten_results;

执行后生成的SQL语句为：

select variant_col:"0.text",
variant_col:legal_name from notation_issue

该语句可正常执行，正确获取带点键的值。

逻辑说明 #

• 当PATH值以['开头（对应含特殊字符的键），用REGEXP_REPLACE提取['和']之间的键名，并用双引号包裹，转换为"0.text"格式，适配点符号访问规则。
• 普通键的PATH值保持原样，直接拼接为variant_col:legal_name格式。

额外说明 #

这种格式不一致属于Snowflake对特殊字符键的特殊处理逻辑，你可以提交支持工单反馈该问题。当前的字符串处理方式作为临时workaround，可满足动态构造SQL的需求。

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