我有一个数据库，其结构和数据类似于以下SQL代码：

CREATE TABLE folder_tree (
    id SERIAL PRIMARY KEY,
    name TEXT,
    level INTEGER,
    parent_id INTEGER,
    created_at TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES folder_tree (id) ON DELETE CASCADE);

INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section A', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1', 1, 2, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section A.1', 1, 1, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section C', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1.1', 2, 3, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1.2', 2, 3, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.2', 1, 2, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section C.1', 1, 5, NOW());

我的目的是根据level和其父id对数据进行排序和分组，然后以JSON格式显示结果。

WITH json_agg_scope_cte AS (
  WITH scope_cte AS (
    WITH RECURSIVE sub_scopes_cte AS (
      SELECT
        s.*,
        NULL :: JSON AS sub_scopes
      FROM
        folder_tree s
      WHERE NOT EXISTS(
          SELECT 1
          FROM folder_tree
          WHERE parent_id = s.id
      )

      UNION ALL

      SELECT
        s.*,
        row_to_json(ssc) AS sub_scopes
      FROM
        sub_scopes_cte ssc
        JOIN folder_tree s
          ON s.id = ssc.parent_id
    )

    SELECT
      id,
      name,
      json_agg(sub_scopes) AS sub_scopes
    FROM sub_scopes_cte ssc
    WHERE parent_id IS NULL
    GROUP BY 1, 2
  )

  SELECT
    s.*,
    sc.sub_scopes
  FROM folder_tree s
    INNER JOIN scope_cte sc
      ON s.id = sc.id
)

SELECT json_agg(json_agg_scope_cte.*) AS scopes
FROM json_agg_scope_cte

我的预期输出序列：

├── A
│   └── A.1
├── B
│   ├── B.1
│   │   ├── B.1.1
│   │   └── B.1.2
│   └── B.2
└── C
    └── C.1

但是在执行查询之后，JSON中的输出序列显示为如下结构：

├── A
│   └── A.1
├── B
│   ├── B.2
│   ├── B.1
│   │   └── B.1.1
│   └── B.1
│       └── B.1.2
└── C
    └── C.1

那么上面的SQL有什么问题，有没有其他的解决方案呢？谢谢