Snowflake Variant列JSON值按条件解析为多列的问题排查

你的SQL执行结果不符合预期的核心问题有两点：

1. 你用array_to_string将department数组拼接为了逗号分隔的完整字符串，比如第一条记录的dept字段实际输出为Electrical,Electronics,Chemical，而你后续的CASE WHEN判断逻辑是该字段完全等于单个部门名称，所有条件自然无法匹配，最终返回NULL。
2. 你的赋值规则要求符合条件的字段赋值为1，但你写的dept2赋值为2、dept3赋值为3，和要求不符。

直接用数组包含函数判断指定部门是否存在即可，不需要做数组转字符串的处理，不同数据库语法稍有差异：

Snowflake/ Databricks 语法 #

select 
  test_column:labels:department_id::varchar as dept_id,
  case when array_contains('Electrical'::variant, test_column:labels:department) then 1 else null end as dept_1,
  case when array_contains('Electronics'::variant, test_column:labels:department) then 1 else null end as dept_2,
  case when array_contains('Chemical'::variant, test_column:labels:department) then 1 else null end as dept_3
from table_a

PostgreSQL 语法 #

select 
  test_column -> 'labels' ->> 'department_id' as dept_id,
  case when test_column -> 'labels' -> 'department' ? 'Electrical' then 1 else null end as dept_1,
  case when test_column -> 'labels' -> 'department' ? 'Electronics' then 1 else null end as dept_2,
  case when test_column -> 'labels' -> 'department' ? 'Chemical' then 1 else null end as dept_3
from table_a

上述SQL执行后即可得到你给出的预期输出结果。

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