如何以嵌套的JSON数组的形式从数据库中获取数据？

import json
import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# 构建查询语句
query = """
    SELECT parent.id, parent.name, JSON_ARRAYAGG(child.name) AS children
    FROM parent
    LEFT JOIN child ON parent.id = child.parent_id
    GROUP BY parent.id, parent.name
"""

# 执行查询
cursor = conn.cursor()
cursor.execute(query)

# 处理结果集
result = []
for row in cursor.fetchall():
    parent_id, parent_name, children = row
    parent = {
        "id": parent_id,
        "name": parent_name,
        "children": json.loads(children) if children else []
    }
    result.append(parent)

# 嵌套JSON数组
nested_json_array = json.dumps(result)

# 关闭数据库连接
cursor.close()
conn.close()

print(nested_json_array)