json - PostgreSQL 13 - 如何排序递归 JSON 层次结构

Question

我需要将文档结构表示为要传递给 Web 应用程序以形成分层树的 JSON 对象。当然，组件的顺序需要正确，所以我正在寻找一种方法来订购父母和孩子。所以每个孩子都应该按 child_order asc 排序

我怎么能那样做？

这是 DDL：

CREATE TABLE documents_structure (
     id INTEGER NOT NULL
   , title CHARACTER VARYING(255) NOT NULL
   , parent_id INTEGER
   , child_order INTEGER NOT NULL
);

插入查询：

INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (1,'Root of the document', NULL,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (2,'First Child of root', 1,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (3,'Second Child of root', 1,2);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (4,'Third Child of root', 1,3);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (5,'First Child of Branch 1', 2,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (6,'Second Child of Branch 1', 2,2);

以及检索 JSON 层次结构的查询：

WITH RECURSIVE document_tree("id", "title", "parent_id", "child_order", "children") AS (
  -- tree leaves (no matching children)
  SELECT c.id,c.title,c.parent_id,c.child_order, json '[]'
  FROM documents_structure c
  WHERE NOT EXISTS(SELECT id,title,parent_id,child_order FROM documents_structure AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)
  UNION ALL
  SELECT (parent).id,(parent).title,(parent).parent_id,(parent).child_order, json_agg(child) AS "children"
  FROM (
    SELECT parent, child
    FROM document_tree AS child
    JOIN documents_structure parent ON parent.id = child.parent_id
  ) branch
  GROUP BY branch.parent
)
SELECT jsonb_pretty(json_agg(t)::jsonb)
FROM document_tree t
LEFT JOIN documents_structure AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id = 1;

为了您的方便，我制作了一个数据库小提琴

标签： jsonpostgresqlhierarchical-data