如何在BigQuery表中解析值为字典的嵌套对象并提取所有name字段？

这个需求在处理半结构化JSON数据时很常见，我来给你提供适配不同列存储类型的解决方案：

情况1：列存储为STRING类型 #

假设你的表名为your_table，存储嵌套字典的列名为nested_dict，可以用以下SQL提取所有name值：

SELECT
  JSON_EXTRACT_SCALAR(nested_dict, CONCAT('$.', key, '.name')) AS name_value
FROM
  your_table,
  UNNEST(JSON_OBJECT_KEYS(nested_dict)) AS key

情况2：列存储为JSON类型 #

如果你的列已经是BigQuery原生的JSON类型，SQL写法几乎一致，因为JSON_OBJECT_KEYS同样支持直接处理JSON类型：

SELECT
  JSON_EXTRACT_SCALAR(nested_dict, CONCAT('$.', key, '.name')) AS name_value
FROM
  your_table,
  UNNEST(JSON_OBJECT_KEYS(nested_dict)) AS key

代码逻辑拆解 #

• JSON_OBJECT_KEYS(nested_dict)：提取嵌套字典的所有顶层键（比如示例中的10037、10403等），返回一个字符串数组
• UNNEST(...) AS key：将数组展开为单独的行，每一行对应一个顶层键
• CONCAT('$.', key, '.name')：拼接出每个键对应的name字段的JSON路径，比如$.10037.name
• JSON_EXTRACT_SCALAR(...)：根据拼接好的路径提取name字段的字符串值

可选优化：过滤NULL值 #

如果某些顶层键对应的对象没有name字段，结果会返回NULL，你可以添加WHERE子句过滤掉这些空值：

SELECT
  JSON_EXTRACT_SCALAR(nested_dict, CONCAT('$.', key, '.name')) AS name_value
FROM
  your_table,
  UNNEST(JSON_OBJECT_KEYS(nested_dict)) AS key
WHERE
  JSON_EXTRACT_SCALAR(nested_dict, CONCAT('$.', key, '.name')) IS NOT NULL

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