如何获取BigQuery嵌套记录的动态嵌套键及实现相关统计

我通过ELT工具将数据导入BigQuery，schema会自动生成或扩展，其中properties字段下包含动态嵌套键。尝试查询my_schema.INFORMATION_SCHEMA.COLUMNS获取键列表时，只能拿到一级键，无法获取嵌套在重复记录里的二级键。我的核心需求有两个：

1. 基于二级嵌套属性properties.filters.[filter_type]的非空值做分组统计，且schema会随应用新增过滤器动态变化，不能硬编码嵌套键；
2. 编写SQL为每条记录添加enabled_filters字段，聚合该记录中非空的属性列表，示例如下：

输入：

输出：

解决方案 #

一、获取properties.filters下的所有动态嵌套键 #

BigQuery的INFORMATION_SCHEMA.COLUMNS中，嵌套字段的column_name会以点分隔完整路径。通过筛选路径前缀为properties.filters.的记录，即可提取所有动态filter类型：

SELECT
  REPLACE(column_name, 'properties.filters.', '') AS filter_type
FROM
  `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = '你的表名'
  AND column_name LIKE 'properties.filters.%'

二、生成enabled_filters字段 #

根据properties字段的类型（JSON/STRUCT），分两种实现方式：

方式1：properties为JSON类型

利用JSON路径动态提取属性值，再过滤非空值生成数组：

WITH filter_keys AS (
  -- 获取所有filter类型
  SELECT
    REPLACE(column_name, 'properties.filters.', '') AS filter_type
  FROM
    `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
  WHERE
    table_name = '你的表名'
    AND column_name LIKE 'properties.filters.%'
),
record_filters AS (
  SELECT
    search_id,
    -- 生成键值对数组
    ARRAY(
      SELECT AS STRUCT
        filter_type,
        JSON_VALUE(properties, CONCAT('$.filters.', filter_type)) AS filter_value
      FROM filter_keys
    ) AS filter_pairs
  FROM `你的项目ID.你的数据集ID.你的表名`
)
SELECT
  search_id,
  -- 过滤非空值，提取键名组成数组
  ARRAY(SELECT filter_type FROM UNNEST(filter_pairs) WHERE filter_value IS NOT NULL) AS enabled_filters
FROM record_filters

方式2：properties为STRUCT类型

STRUCT字段无法通过JSON路径动态访问，需用动态SQL生成查询逻辑：

DECLARE filter_columns STRING;

-- 拼接非空判断逻辑
SET filter_columns = (
  SELECT STRING_AGG(
    CONCAT('IF(properties.filters.', filter_type, ' IS NOT NULL, "', filter_type, '", NULL)'),
    ', '
  )
  FROM (
    SELECT
      REPLACE(column_name, 'properties.filters.', '') AS filter_type
    FROM
      `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
    WHERE
      table_name = '你的表名'
      AND column_name LIKE 'properties.filters.%'
  )
);

-- 执行动态生成的查询
EXECUTE IMMEDIATE CONCAT('
  SELECT
    search_id,
    ARRAY(SELECT val FROM UNNEST([', filter_columns, ']) val WHERE val IS NOT NULL) AS enabled_filters
  FROM `你的项目ID.你的数据集ID.你的表名`
');

三、基于动态filter字段分组统计 #

同样用动态SQL实现，以统计每个filter类型的非空值分布为例：

DECLARE filter_stats_sql STRING;

SET filter_stats_sql = (
  SELECT STRING_AGG(
    CONCAT(
      'SELECT "', filter_type, '" AS filter_type, properties.filters.', filter_type, ' AS filter_value, COUNT(*) AS count FROM `你的项目ID.你的数据集ID.你的表名` WHERE properties.filters.', filter_type, ' IS NOT NULL GROUP BY 1,2'
    ),
    ' UNION ALL '
  )
  FROM (
    SELECT
      REPLACE(column_name, 'properties.filters.', '') AS filter_type
    FROM
      `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
    WHERE
      table_name = '你的表名'
      AND column_name LIKE 'properties.filters.%'
  )
);

EXECUTE IMMEDIATE filter_stats_sql;

内容的提问来源于stack exchange，提问作者Cyril Duchon-Doris