Google BigQuery嵌套JSON扁平化及Pandas json_normalize报错解决

嘿，我来帮你搞定这两个问题——BigQuery里扁平化嵌套JSON列，还有Pandas json_normalize报错的事儿！

首先得明确你的JSON列在BigQuery里是STRING类型还是已经被自动解析成STRUCT类型，两种情况的处理方式略有不同：

情况1：JSON列是STRING类型 #

如果你的列是纯字符串格式的JSON（比如列名叫json_data），可以用BigQuery的JSON_VALUE函数直接提取每个嵌套字段，语法非常直观：

SELECT
  JSON_VALUE(json_data, '$.name') AS name,
  JSON_VALUE(json_data, '$.last_delivered.push_id') AS last_delivered_push_id,
  JSON_VALUE(json_data, '$.last_delivered.time') AS last_delivered_time,
  JSON_VALUE(json_data, '$.session_id') AS session_id,
  JSON_VALUE(json_data, '$.source') AS source,
  JSON_VALUE(json_data, '$.properties.UserId') AS properties_UserId
FROM
  `your-project.your-dataset.your-table`

如果嵌套字段里有非字符串类型（比如数字、布尔值），可以用JSON_EXTRACT配合CAST转换类型，比如：

CAST(JSON_EXTRACT(json_data, '$.some_numeric_field') AS INT64) AS some_numeric_field

情况2：JSON列已经是STRUCT类型 #

如果BigQuery在导入数据时自动把JSON解析成了STRUCT类型（这很常见，尤其是用JSON格式导入时），直接用点符号访问嵌套字段就行，更简单：

SELECT
  json_data.name AS name,
  json_data.last_delivered.push_id AS last_delivered_push_id,
  json_data.last_delivered.time AS last_delivered_time,
  json_data.session_id AS session_id,
  json_data.source AS source,
  json_data.properties.UserId AS properties_UserId
FROM
  `your-project.your-dataset.your-table`

你用json_normalize(a)持续报错，大概率是输入数据的格式不符合要求——json_normalize默认需要的是字典组成的列表，如果是单个字典，得额外指定参数。我给你一步步捋：

步骤1：确保JSON数据被正确解析为Python字典 #

如果你的数据是从BigQuery导出的字符串格式JSON，第一步要把它转成Python字典，不然json_normalize没法处理：

import json
import pandas as pd

# 示例：如果是字符串格式的JSON，先解析成字典
json_str = '{"name": "name1", "last_delivered": {"push_id": "push_id1", "time": "time1"}, "session_id": "session_id1", "source": "SDK", "properties": {"UserId": "u1"}}'
data = json.loads(json_str)

步骤2：正确调用json_normalize #

• 如果是单个字典：必须指定record_path=None，否则会报错找不到数据路径：

df = pd.json_normalize(data, record_path=None)

执行后你就能得到扁平化的DataFrame，列名就是name、last_delivered.push_id、last_delivered.time这些你要的字段。

• 如果是多个字典组成的列表（比如从BigQuery导出的多条数据）：直接把列表传进去就行，不需要额外参数：

# 示例多条数据
data_list = [
    {"name": "name1", "last_delivered": {"push_id": "push_id1", "time": "time1"}, ...},
    {"name": "name2", "last_delivered": {"push_id": "push_id2", "time": "time2"}, ...}
]
df = pd.json_normalize(data_list)

常见报错排查 #

• 报错Expected object or value：说明你的输入不是有效的JSON字符串或字典，检查一下有没有语法错误（比如引号不匹配、逗号漏写）。
• 报错KeyError: ...：说明你指定的record_path不存在，或者数据结构和你预期的不一样，先打印data看看实际的结构再调整。
• 如果是从BigQuery直接读取数据后处理：确保你把JSON列先解析成字典，比如：

# 假设从BigQuery读取的DataFrame里有一列叫json_data（字符串类型）
df_bq = pd.read_gbq("SELECT json_data FROM your_table")
df_bq['json_data'] = df_bq['json_data'].apply(json.loads)
df_flat = pd.json_normalize(df_bq['json_data'])

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