如何在BigQuery中以类通配符方式查询多个同字段STRUCT结构

标准SQL没有原生支持%.qty这类跨STRUCT同名字段的通配符查询语法，但可以通过逆透视（列转行）的方式，在不修改现有表结构的前提下实现批量查询筛选，不同SQL引擎的实现方式如下：

BigQuery 实现方案 #

通过内置UNPIVOT语法将多个STRUCT字段转为行维度统一处理：

WITH unpivoted_data AS (
  -- 保留原表除4个水果STRUCT外的所有字段
  SELECT * EXCEPT(mango, melon, apple, banana),
    fruit_type,
    fruit.qty,
    fruit.price
  FROM `你的表完整路径`
  -- 把4个STRUCT列转成 fruit_type（水果名称）+ fruit（对应STRUCT）的行数据
  UNPIVOT (
    fruit FOR fruit_type IN (mango, melon, apple, banana)
  )
)
-- 统一筛选qty大于5的记录
SELECT * FROM unpivoted_data WHERE qty > 5;

如果你的需求是只要任意一个水果的qty>5就保留整条原记录、不需要拆分多行的话，直接用多条件OR即可：

SELECT * FROM `你的表完整路径`
WHERE mango.qty > 5 
   OR melon.qty > 5
   OR apple.qty > 5
   OR banana.qty > 5;

Spark SQL / 其他支持数组爆炸语法的引擎实现方案 #

通过构造STRUCT数组+爆炸的方式实现逆透视：

SELECT 
  * EXCEPT(mango, melon, apple, banana),
  fruit_info.fruit_type,
  fruit_info.fruit.qty,
  fruit_info.fruit.price
FROM 你的表名
LATERAL VIEW EXPLODE(
  ARRAY(
    STRUCT('mango' AS fruit_type, mango AS fruit),
    STRUCT('melon' AS fruit_type, melon AS fruit),
    STRUCT('apple' AS fruit_type, apple AS fruit),
    STRUCT('banana' AS fruit_type, banana AS fruit)
  )
) exploded_table AS fruit_info
WHERE fruit_info.fruit.qty > 5;

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