如何基于相同ID将多个JSON合并为指定结构的父JSON？

首先需要指出你提供的原始JSON存在语法错误：外层应该使用**数组（[]）**而非对象（{}），否则无法通过JSON解析。修正后的原始JSON如下：

修正后的JSON1 #

[
    {
        "id":"1",
        "name":"testname1",
        "class":"testclass1"
    },
    {
        "id":"2",
        "name":"testname2",
        "class":"testname2"
    }
]

修正后的JSON2 #

[
    {
        "id":"1",
        "json1id":"1",
        "semester1math":"90",
        "semester1science":"80",
        "semester1history":"85"
    },
    {
        "id":"2",
        "json1id":"1",
        "semester2math":"88",
        "semester2science":"75",
        "semester2history":"80"
    },
    {
        "id":"3",
        "json1id":"1",
        "semester3math":"90",
        "semester3science":"95",
        "semester3history":"85"
    },
    {
        "id":"4",
        "json1id":"2",
        "semester1math":"80",
        "semester1science":"80",
        "semester1history":"90"
    },
    {
        "id":"5",
        "json1id":"2",
        "semester2math":"75",
        "semester2science":"86",
        "semester2history":"82"
    },
    {
        "id":"6",
        "json1id":"2",
        "semester3math":"78",
        "semester3science":"85",
        "semester3history":"100"
    }
]

同时你期望的目标结构中，marks字段应该是数组（否则JSON语法不合法），修正后的目标结构示例：

[
    {
        "id":"1",
        "name":"testname1",
        "class":"testclass1",
        "marks": [
            {
                "id":"1",
                "json1id":"1",
                "semester1math":"90",
                "semester1science":"80",
                "semester1history":"85"
            },
            {
                "id":"2",
                "json1id":"1",
                "semester2math":"88",
                "semester2science":"75",
                "semester2history":"80"
            },
            {
                "id":"3",
                "json1id":"1",
                "semester3math":"90",
                "semester3science":"95",
                "semester3history":"85"
            }       
        ]
    },
    {
        "id":"2",
        "name":"testname2",
        "class":"testname2",
        "marks": [
            {
                "id":"4",
                "json1id":"2",
                "semester1math":"80",
                "semester1science":"80",
                "semester1history":"90"
            },
            {
                "id":"5",
                "json1id":"2",
                "semester2math":"75",
                "semester2science":"86",
                "semester2history":"82"
            },
            {
                "id":"6",
                "json1id":"2",
                "semester3math":"78",
                "semester3science":"85",
                "semester3history":"100"
            }
        ]
    }
]

解决方案：应用层处理嵌套合并 #

SQL的JOIN操作只能返回扁平的行数据，无法直接生成嵌套的数组结构。你需要在业务应用层对查询结果（或已加载的JSON数据）进行二次处理，以下是Python的实现示例：

import json

# 加载修正后的JSON数据
json1_data = json.loads('''[
    {"id":"1","name":"testname1","class":"testclass1"},
    {"id":"2","name":"testname2","class":"testname2"}
]''')

json2_data = json.loads('''[
    {"id":"1","json1id":"1","semester1math":"90","semester1science":"80","semester1history":"85"},
    {"id":"2","json1id":"1","semester2math":"88","semester2science":"75","semester2history":"80"},
    {"id":"3","json1id":"1","semester3math":"90","semester3science":"95","semester3history":"85"},
    {"id":"4","json1id":"2","semester1math":"80","semester1science":"80","semester1history":"90"},
    {"id":"5","json1id":"2","semester2math":"75","semester2science":"86","semester2history":"82"},
    {"id":"6","json1id":"2","semester3math":"78","semester3science":"85","semester3history":"100"}
]''')

# 构建json1的id到对象的映射，并初始化marks数组
id_to_student = {}
for student in json1_data:
    student['marks'] = []
    id_to_student[student['id']] = student

# 遍历json2数据，将对应记录添加到学生的marks数组中
for mark_record in json2_data:
    student_id = mark_record['json1id']
    if student_id in id_to_student:
        id_to_student[student_id]['marks'].append(mark_record)

# 转换为最终的JSON数组
merged_json = list(id_to_student.values())

# 打印格式化后的结果
print(json.dumps(merged_json, indent=4))

代码说明： #

1. 先将JSON数据加载为Python列表/字典。
2. 创建字典映射学生ID到对应的学生对象，同时为每个学生初始化marks空数组。
3. 遍历成绩记录，根据json1id将记录添加到对应学生的marks数组中。
4. 最后将映射字典的值转换为数组，得到合并后的嵌套结构JSON。

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