Python中基于order_id嵌套合并两个JSON对象的方法

你当前代码里直接拼接两个JSON字符串的方式是错误的——这样得到的是无效的JSON格式，完全达不到嵌套的效果。我们可以在DataFrame层面或者字典列表层面完成关联嵌套，下面给你两种可行的方案：

方案一：用Pandas内置方法高效处理（推荐） #

这种方法利用Pandas的分组和合并功能，代码简洁且适合处理较大数据集：

from func.excelfunction import *
from datetime import datetime as dt
from datetime import timedelta
import requests
import csv
import os
import math
from sql_server.sql_server import *
import pandas as pd
import json

# variables
dir_path = os.path.dirname(os.path.realpath(__file__))
df1 = run_sql_df('SET NOCOUNT ON; exec isawitfirstdb.dbo.usp_Torque_orderDetails')
df1 = df1.astype({"created_at":"str","order_number":"str","order_id":"int64","instructions":"str","dispatch_method":"str","email":"str","Contact":"str", 
"name":"str","address_1":"str","address_2":"str","town":"str","county":"str","postcode":"str","country":"str","invoice_currency":"str", 
"subtotal_price":"str","total_price":"str","invoice_name":"str","invoice_contact_phone":"str","invoice_address_1":"str","invoice_address_2":"str", 
"invoice_town":"str","invoice_county":"str","invoice_postcode":"str","inovice_country":"str","Owner_id":"str","Merge_status":"str","Merge_action":"str","Record_type":"str"})
df2 = run_sql_df('SET NOCOUNT ON; exec isawitfirstdb.dbo.usp_Torque_orderLineDetails')
df2 = df2.astype({"sku_id":"str","order_id":"int64","qty_ordered":"int64","user_def_type_1":"str","user_def_type_2":"str","user_def_num_1":"str","line_value":"int64","config_id":"str","Merge_status":"str","Merge_action":"str","Record_type":"str"})

# 保留DataFrame格式，先不急着转JSON
orders = pd.DataFrame(df1)
orderlines = pd.DataFrame(df2)

# 1. 按order_id分组，把每组的订单行转成字典列表
orderlines_grouped = orderlines.groupby('order_id').apply(lambda x: x.to_dict('records')).reset_index(name='orderlines')

# 2. 合并主订单和分组后的订单行，左连接确保所有主订单都被保留
merged_orders = pd.merge(orders, orderlines_grouped, on='order_id', how='left')

# 3. 给没有订单行的主订单填充空列表，避免出现null
merged_orders['orderlines'] = merged_orders['orderlines'].apply(lambda x: x if isinstance(x, list) else [])

# 4. 转换为符合要求的嵌套JSON
final_json = merged_orders.to_json(orient='records', indent=2)
print(final_json)

方案二：用字典映射手动处理（更直观） #

如果你对Pandas的分组操作不太熟悉，这种方法通过字典映射来匹配订单ID，逻辑更清晰：

# 前面的导入、变量定义、DataFrame获取和类型转换代码和上面一致

# 先把两个DataFrame转成字典列表
orders_list = orders.to_dict('records')
orderlines_list = orderlines.to_dict('records')

# 1. 构建订单ID到订单行的映射字典
orderlines_map = {}
for line in orderlines_list:
    order_id = line['order_id']
    if order_id not in orderlines_map:
        orderlines_map[order_id] = []
    orderlines_map[order_id].append(line)

# 2. 遍历每个主订单，添加对应的订单行
for order in orders_list:
    order_id = order['order_id']
    # 用get方法，没有对应订单行就返回空列表
    order['orderlines'] = orderlines_map.get(order_id, [])

# 3. 转换为JSON
final_json = json.dumps(orders_list, indent=2)
print(final_json)

关键说明 #

• 两种方案都会生成以orderdetails为主、orderlines作为嵌套数组的JSON结构，每个主订单对象里都会有一个orderlines字段，值是该订单对应的所有订单行列表。
• 如果你有部分订单没有对应的订单行，代码会自动给这些订单的orderlines填充空列表，避免出现null值。

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