如何使用Python将含对象数组的嵌套JSON转换为Excel？

需要将包含对象数组的嵌套JSON转换为指定列格式的Excel，目标列如下：
metaData.user report.from report.to lossInfo.locationName.name lossInfo.locationName.locNbr lossInfo.locationDetails.locNm lossInfo.locationDetails.locAddress.locCity

预期输出示例：

代码实现 #

import pandas as pd
import json

# 加载JSON数据（若为文件，可替换为with open('data.json', 'r') as f: json_data = json.load(f)）
json_data = {
    "metaData": {
        "formName": "A1",
        "user": "Test User"
    },
    "report": {
        "from": "12/12/2021",
        "to": "12/12/2022"
    },
    "lossInfo": [
        {
            "locationName": {
                "name": "test1",
                "locNbr": "12"
            },
            "locationDetails": {
                "locNm": "xyz",
                "locAddress": {
                    "locCity": "abc",
                    "locStateCd": "abcd"
                },
                "state": "ab",
                "lossLocation": "cd"
            }
        },
        {
            "locationName": {
                "name": "test11",
                "locNbr": "121"
            },
            "locationDetails": {
                "locNm": "xyz1",
                "locAddress": {
                    "locCity": "abc1",
                    "locStateCd": "abcd1"
                },
                "state": "ab1",
                "lossLocation": "cd1"
            }
        }
    ]
}

# 提取公共字段并转换日期格式
common_user = json_data['metaData']['user']
report_from = json_data['report']['from'].replace('/', '-')
report_to = json_data['report']['to'].replace('/', '-')

# 遍历lossInfo数组，整理每行数据
rows = []
for loss_item in json_data['lossInfo']:
    row_data = {
        'metaData.user': common_user,
        'report.from': report_from,
        'report.to': report_to,
        'lossInfo.locationName.name': loss_item['locationName']['name'],
        'lossInfo.locationName.locNbr': loss_item['locationName']['locNbr'],
        'lossInfo.locationDetails.locNm': loss_item['locationDetails']['locNm'],
        'lossInfo.locationDetails.locAddress.locCity': loss_item['locationDetails']['locAddress']['locCity']
    }
    rows.append(row_data)

# 生成DataFrame并保存为Excel
df = pd.DataFrame(rows)
df.to_excel('loss_report.xlsx', index=False)

注意事项 #

1. 依赖安装：执行pip install pandas openpyxl安装所需库（openpyxl用于支持xlsx格式保存）
2. 数据适配：若JSON来自外部文件，替换代码中json_data的赋值逻辑为文件读取即可
3. 日期处理：代码中通过字符串替换完成日期格式转换，若需更复杂的日期解析，可使用datetime模块处理

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