postgres中的创意json树结构

Question

我试图创建一个JSON来表示任意深度和广度的层次结构，例如生物：

CREATE TABLE creatures (
    name text PRIMARY KEY,
    parent text REFERENCES creatures(name)
);

INSERT INTO creatures(name,parent)
VALUES
('amoeba',NULL),
('beetle','amoeba'),
('coelacanth','amoeba'),
('salmon','coelacanth'),
('tuna','coelacanth'),
('lizard','coelacanth'),
('t-rex','lizard'),
('plant',NULL);

我想把它转换成这样的JSON：

[{"name":"amoeba",
  "children": [{"name": "beetle",
                "children": []}, 
               {"name": "coelacanth",
                "children": [{"name": "tuna",
                              "children": []}, 
                             {"name": "salmon",
                              "children": []} 
                             {"name": "lizard",
                              "children": [{"name": "t-rex",
                                            "children": []}]}
                             ]}]},
 {"name": "plant",
  "children": []}]

这在博士后中可行吗？

到目前为止我已经试过了

WITH RECURSIVE r AS
    -- Get all the leaf nodes, group by parent.
    (SELECT parent,
            json_build_object('name', parent, 
                              'children', array_agg(name)) AS json
     FROM creatures c
     WHERE parent NOTNULL
         AND NOT EXISTS
             (SELECT 1
              FROM creatures c2
              WHERE c.name = c2.parent)
     GROUP BY parent
     
     UNION 
     
     -- Recursive term - go one step up towards the top.
     SELECT c.parent,
            json_build_object('name', c.parent, 
                              'children', array_agg(c.name)) AS json
     FROM r
     JOIN creatures c ON r.parent = c.name
     GROUP BY c.parent)
SELECT *
FROM r;

但它失败了

ERROR:  aggregate functions are not allowed in a recursive query's recursive term
LINE 19:                            'children', array_agg(c.name)) AS...

有没有办法解决这个问题，或者其他解决方案可以让我成为我的好树？

Answer 1

首先，您应该在postgres中使用jsonb格式，而不是json格式，请参阅以下文档：

一般来说，大多数应用程序应该更喜欢将JSON数据存储为jsonb，除非有非常特殊的需求，例如关于对象键排序的遗留假设。。

然后，下面是一种将jsonb转换为文本的方法，因为jsonb替换函数jsonb_集在您的情况下是不可移植的：

CREATE VIEW parent_children (parent, children, root, cond) AS
(   SELECT jsonb_build_object('name', c.parent, 'children', '[]' :: jsonb) :: text AS parent
         , jsonb_agg(jsonb_build_object('name', c.name, 'children', '[]' :: jsonb)) :: text AS children
         , array[c.parent] AS root
         , array[c.parent] AS cond
      FROM creatures AS c
     GROUP BY c.parent
) ;

WITH RECURSIVE list(parent, children, root, cond) AS
(   SELECT children, children, root, cond
      FROM parent_children
     WHERE root = array[NULL]   -- start with the root parents
    UNION
    SELECT p.parent
         , replace(p.children, c.parent, replace(c.parent, '[]', c.children))
         , p.root
         , p.cond || c.cond
      FROM list AS p
     INNER JOIN parent_children AS c
        ON position(c.parent IN p.children) > 0
       AND NOT p.cond @> c.root -- condition to avoid circular path
)
SELECT children :: jsonb
  FROM list AS l
  ORDER BY array_length(cond, 1) DESC
  LIMIT 1 ;

结果是：

[
  {
    "name": "amoeba",
    "children": [
      {
        "name": "beetle",
        "children": []
      },
      {
        "name": "coelacanth",
        "children": [
          {
            "name": "salmon",
            "children": []
          },
          {
            "name": "tuna",
            "children": []
          },
          {
            "name": "lizard",
            "children": [
              {
                "name": "t-rex",
                "children": []
              }
            ]
          }
        ]
      }
    ]
  },
  {
    "name": "plant",
    "children": []
  }
]