如何将含嵌套JSON的Pandas DataFrame正确写入BigQuery？

方式1：将每个Item作为单独行（推荐，结构化存储） #

这种方式把items里的每个条目拆成单独行，offset作为所有行的共同字段，更符合BigQuery的结构化存储规范，方便后续查询分析。

修正后的代码： #

import requests
from requests.auth import HTTPBasicAuth
import pandas
import pandas_gbq

URL = 'xxxxxxxxxxxxxxxxxxxx'
auth = HTTPBasicAuth('name', 'password')
r = requests.get(url=URL, auth=auth)
data = r.json()  # 无需重复json.loads(json.dumps(...))，直接用r.json()即可

# 提取items转为DataFrame，添加offset字段
items_df = pandas.DataFrame(data['data']['items'])
items_df['offset'] = str(data['data']['offset'])

table_id = 'sdm_adpoint.testfapi1'
# BigQuery schema：offset为STRING，id和title为STRING（根据实际字段调整）
schema = [
    {'name': 'offset', 'type': 'STRING'},
    {'name': 'id', 'type': 'STRING'},
    {'name': 'title', 'type': 'STRING'}
]

pandas_gbq.to_gbq(
    items_df,
    table_id,
    project_id='ncau-data-newsquery-sit',
    if_exists='append',
    table_schema=schema
)

说明： #

• 直接将items数组转为DataFrame，每个字典对应一行的字段
• 新增offset列并填充当前分页的偏移值
• BigQuery schema对应每个字段的类型，无需使用REPEATED类型

方式2：保持单行存储，json_data为JSON字符串数组 #

如果需要保留offset对应一行，json_data为包含多个JSON字符串的数组（对应BigQuery的REPEATED STRING类型），可按以下方式处理：

修正后的代码： #

import requests
from requests.auth import HTTPBasicAuth
import json
import pandas
import pandas_gbq

URL = 'xxxxxxxxxxxxxxxxxxxx'
auth = HTTPBasicAuth('name', 'password')
r = requests.get(url=URL, auth=auth)
data = r.json()

offset = str(data['data']['offset'])
# 将每个item字典转为JSON字符串，组成数组
json_data = [json.dumps(item) for item in data['data']['items']]

# 构造DataFrame：一行数据，offset为单个值，json_data为字符串数组
df = pandas.DataFrame({
    'offset': [offset],  # 用列表包裹，避免广播
    'json_data': [json_data]
})

table_id = 'sdm_adpoint.testfapi1'
schema = [
    {'name': 'offset', 'type': 'STRING'},
    {'name': 'json_data', 'type': 'STRING', 'mode': 'REPEATED'}
]

pandas_gbq.to_gbq(
    df,
    table_id,
    project_id='ncau-data-newsquery-sit',
    if_exists='append',
    table_schema=schema
)

说明： #

• 将每个item字典序列化为JSON字符串，确保数组元素是字符串类型，匹配BigQuery的REPEATED STRING schema
• 构造DataFrame时，offset和json_data都用列表包裹，避免pandas自动广播为多行
• 原代码错误地直接将字典数组赋值给json_data列，导致类型不匹配，转为JSON字符串数组后可正常写入

1. DataFrame构造错误：原代码中offset是单个字符串，json_data是字典数组，pandas会自动将offset广播为与json_data长度一致的多行，每行的json_data是单个字典，与BigQuery定义的REPEATED STRING类型不匹配，引发ArrowTypeError。
2. 字符串转换错误：直接转df['json_data']为字符串时，会将整个数组转为单个字符串，导致写入时被拆分为字符列，正确做法是逐个将字典转为JSON字符串再组成数组。

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