Snowflake解析含数组的JSON数据时如何将数组转为逗号分隔字符串

解决方法 #

你可以直接用Snowflake内置的ARRAY_TO_STRING函数处理数组类型的variant值，修改后的查询语句如下：

SELECT 
response:survey_id::varchar AS survey_id, 
response:values:survey_date::varchar AS survey_date, 
ARRAY_TO_STRING(response:values:survey_options_selected::ARRAY, ', ') AS survey_options_selected,
response:values:Time::varchar AS Time
FROM tableA

逻辑说明

• 你之前直接将response:values:survey_options_selected强制转varchar时，会保留JSON数组自带的方括号、引号格式，所以出现了["EMAIL"]这类结果
• 修改后先将该字段值显式转成Snowflake原生数组类型::ARRAY，再通过ARRAY_TO_STRING函数用, 作为分隔符拼接数组中的所有元素，就能得到你要的逗号加空格分隔的纯字符串结果
• 如果需要兼容空数组的场景，可以在外层加COALESCE处理返回默认值，比如COALESCE(ARRAY_TO_STRING(response:values:survey_options_selected::ARRAY, ', '), '') AS survey_options_selected，避免返回NULL值。

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