如何使用python从postgresql中的嵌套CTE输出嵌套json？

import psycopg2
import json

# 连接到PostgreSQL数据库
conn = psycopg2.connect(database="your_database", user="your_username", password="your_password", host="your_host", port="your_port")

# 创建游标对象
cur = conn.cursor()

# 编写包含嵌套CTE的SQL查询语句
sql_query = """
WITH recursive nested_cte AS (
    SELECT id, parent_id, name
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.id, t.parent_id, t.name
    FROM your_table t
    JOIN nested_cte n ON n.id = t.parent_id
)
SELECT jsonb_build_object('id', n.id, 'name', n.name, 'children', jsonb_agg(jsonb_build_object('id', t.id, 'name', t.name)))
FROM nested_cte n
LEFT JOIN your_table t ON t.parent_id = n.id
GROUP BY n.id, n.name;
"""

# 执行SQL查询
cur.execute(sql_query)

# 获取查询结果集
results = cur.fetchall()

# 关闭游标和数据库连接
cur.close()
conn.close()

# 将结果集转换为嵌套JSON对象
nested_json = []
for row in results:
    nested_json.append(row[0])

# 将嵌套JSON对象转换为JSON字符串
json_str = json.dumps(nested_json)

# 打印嵌套JSON字符串
print(json_str)