2

Having this specific simple many to many self referential structure

An item owns other items through the joins table; trying to retrieve the whole tree structure as json while querying a given ite.

For example, while querying the item with id 1 :

SELECT it.*, fulltree from items where id = 1;

Outputting the following :

{
  id: 1,
  title: "PARENT",
  children: [
   {
     id: 2,
     title: "LEVEL 2",
     children: [
      {
        id: 3,
        title: "LEVEL 3.1",
        children: [
          {
            id: 4,
            title: "LEVEL 4.1"
          },
          {
            id: 5,
            title: "LEVEL 4.2"
          }
        ]
      },
      {
        id: 6,
        title: "LEVEL 3.2"
      }
     ]
   }
  ]
}

I've digged into json capabilities postgres offers, managed such output by repeating nested query (simple but ugly, and limitated to the amount of repeats :/)

I've found out about recursive queries. The examples found here and there not being that simple, I'm having had times finding an entrypoint to understand it correctly and adapt those to my needs

Example Data

CREATE TABLE items (
    id    serial primary key,
    title VARCHAR(255)
);

insert into items (title) values ('PARENT');
insert into items (title) values ('LEVEL 2');
insert into items (title) values ('LEVEL 3.1');
insert into items (title) values ('LEVEL 4.1');
insert into items (title) values ('LEVEL 4.2');
insert into items (title) values ('LEVEL 3.2');

CREATE TABLE joins (
  id serial primary key,
  item_id INT,
  child_id INT
);

insert into joins (item_id, child_id) values (1,2);
insert into joins (item_id, child_id) values (2,3);
insert into joins (item_id, child_id) values (3,4);
insert into joins (item_id, child_id) values (3,5);
insert into joins (item_id, child_id) values (2,6);

I hope the example here will be simple enough to find help from experienced users; in advanced thx a lot

  • 1
    Does one child ever have two parents? – Evan Carroll Jan 17 '18 at 16:48
  • @EvanCarroll Found many examples with setup on single table having a parent_id column (ltree style, or ancestry… don't know how to call it)... and that would have been simpler I guess. So my answer to your question is de facto yes. I'd be glad to understand how it could be done that way, or understand why it should not be done that way. thx for the edit! the link might not be that visible for sure – Ben Jan 17 '18 at 17:06
0

Here is an example query,

WITH RECURSIVE t(item_id, json) AS (
        SELECT item_id, to_jsonb(items)
        FROM items
        WHERE NOT EXISTS (
                SELECT 1
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;
 item_id |             jsonb_pretty              
---------+---------------------------------------
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.2",    +
         |             "item_id": 6             +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.1",+
         |                 "item_id": 4         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.2",+
         |                 "item_id": 5         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
(3 rows)

Note, we're not actually merging the paths to form a completed tree. You either have to build the tree from the root node down, or the leaf nodes to the top. In this case, you'll have to merge the discrete paths. Look for a deep json merge in Javascript and tie it together with plv8

Test data

I modified your example schema a bit,

DROP TABLE joins;
CREATE TABLE items (
  item_id     serial PRIMARY KEY,
  title text
);
CREATE TABLE joins (
  id          serial PRIMARY KEY,
  item_id     int,
  child_id    int
);
INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.