Creative json tree structure in postgres

Question

I'm trying to create a JSON representing an arbitrarily deep and wide hierarchy, for example of creatures:

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);

I want to turn this into a JSON like this:

[{"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": []}]

Is this possible to do in Postgres?

So far I've tried

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;

But it fails with

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

Is there any way to work around this, or another solution that can make me my nice tree?

Use a recursive function. And ensure that your creatures do not contain circular paths… — Bergi
– Bergi, Commented Oct 21, 2021 at 3:08
@Bergi. There are no circular paths. And as I described in my question that is what I am trying to do.. — rutchkiwi
– rutchkiwi, Commented Oct 21, 2021 at 10:42
Well but Postgres doesn't know that, so it refuses to do a union with recursive query that keeps generating rows forever… — Bergi
– Bergi, Commented Oct 21, 2021 at 18:19
I believe this is the query that you attempted, but that's not actually doing something recursive — Bergi
– Bergi, Commented Oct 21, 2021 at 18:30

Edouard · Accepted Answer · 2021-10-22 06:50:15Z

First you should use the jsonb format instead of the json format in postgres, see the documentation here :

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys..

Then, here below is a way to get your result converting jsonb into text, because the jsonb replace function jsonb_set is unconfortable in your case :

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 ;

The result is :

[
  {
    "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": []
  }
]

Collectives™ on Stack Overflow

Creative json tree structure in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related