如何在BigQuery中查询含字典嵌套列表结构的表并展开数据

你可以通过解析JSON字符串、展开数组并映射字段的方式实现需求，以下是具体查询语句：

WITH parsed_table AS (
  SELECT
    idx,
    -- 将字符串类型的info解析为JSON对象
    JSON_PARSE(info) AS json_info
  FROM
    `你的项目ID.你的数据集ID.你的表名`
)
SELECT
  idx,
  -- 提取data子数组的第一个元素作为name
  data_item[OFFSET(0)] AS name,
  -- 提取data子数组的第二个元素作为age，可根据需要转换类型
  CAST(data_item[OFFSET(1)] AS INT64) AS age
FROM
  parsed_table,
  -- 展开json_info中的data数组，每个子数组生成一行
  UNNEST(JSON_EXTRACT_ARRAY(json_info, '$.data')) AS data_item

关键步骤说明 #

• 解析JSON字符串：使用JSON_PARSE函数将字符串类型的info列转换为可操作的JSON对象
• 展开数组：通过UNNEST(JSON_EXTRACT_ARRAY(json_info, '$.data'))将data数组中的每个子数组拆分为单独的行
• 映射字段：利用数组的OFFSET索引，按columns定义的顺序提取对应字段，同时可通过CAST转换字段类型以匹配需求

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