如何从变体列的两个不同JSON条目中查询指定字段？

问题分析 #

你的表中variant列存储了两种结构的JSON数据：

• 结构1：包含key_a、key_b、key_c字段
• 结构2：包含key_d、key_e字段

需要筛选出满足key_c = 'some_value'且key_b = key_e的记录，配对提取对应的key_a和key_d。

正确实现方式（以Snowflake为例） #

针对Snowflake的variant类型（常见的JSON存储类型），可以通过JSON路径提取字段，再通过JOIN关联两类记录：

SELECT 
    t1.variant_col:key_a::STRING AS key_a,
    t2.variant_col:key_d::STRING AS key_d
FROM 
    your_table t1
JOIN 
    your_table t2 ON t1.variant_col:key_b::INT = t2.variant_col:key_e::INT
WHERE 
    t1.variant_col:key_c::STRING = 'some_value'
    -- 可选：过滤确保只处理对应结构的行
    AND t1.variant_col:key_a IS NOT NULL
    AND t2.variant_col:key_d IS NOT NULL;

关键说明： #

• variant_col:key_a::STRING：从variant列提取指定JSON字段，并转换为对应数据类型（数字类型用::INT，根据实际字段调整）
• 通过表自关联（用别名区分两类记录），匹配key_b和key_e的等值关系
• WHERE子句精准过滤出key_c符合条件的结构1记录

原查询的错误原因 #

你之前的CTE写法存在两个核心问题：

1. 同级定义的CTE（group_1、group_2）属于独立数据集，无法直接互相引用字段，group_2中使用group_1.A不符合语法规则
2. UNION操作要求合并的数据集列数、类型完全一致，你的写法无法将单列结果合并成包含key_a和key_d的行，必然导致结构不匹配

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