5

Having this simple many-to-many self-referential structure.
An item owns other items through the joins table:

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

db<>fiddle here

I am trying to retrieve a whole tree structure as JSON for a given item.
For example, to query the item with item_id 1 (pseudo-code):

SELECT i.*, fulltree from items i where item_id = 1;

Desired output for fulltree:

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

After digging into the JSON capabilities Postgres offers, I managed such output by repeating a nested query. Simple but ugly, and limited to the amount of repeats. :/

I've found out about recursive queries. The examples found here and there are not that simple. It's hard to finding an entrypoint to understanding the technique and adapt it to my needs.

I hope the example here will be simple enough to find help from experienced users.

3
  • 1
    Does one child ever have two parents? Jan 17, 2018 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, 2018 at 17:06
  • @ISupportTheBoycott: For the purpose of this question, the number of parents seems to make no difference. Jun 12, 2021 at 1:35

2 Answers 2

3

A recursive CTE (rCTE) does not allow aggregation in the recursive term. So there is no simple solution.

I suggest a recursive function for an elegant solution:

CREATE OR REPLACE FUNCTION f_item_tree(_item_id int)
  RETURNS jsonb
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT jsonb_agg(sub)
FROM  (
   SELECT i.*, f_item_tree(i.item_id) AS children
   FROM   joins j
   JOIN   items i ON i.item_id = j.child_id
   WHERE  j.item_id = _item_id
   ORDER  BY i.item_id
   ) sub
$func$;

fiddle

Bare call:

SELECT to_jsonb(sub) AS tree
FROM  (
   SELECT *, f_item_tree(item_id) AS children
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;

To strip objects with NULL value (no children) and prettify:

SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
   SELECT *, f_item_tree(item_id) AS children
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;

Produces your desired output exactly (one complete tree):

{
    "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
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}

jsonb_strip_nulls() ...

Deletes all object fields that have null values from the given JSON value, recursively.

If there can be other fields with null values that you want to keep, you have to do more. Like:

CREATE OR REPLACE FUNCTION f_item_tree(_item_id int)
  RETURNS jsonb
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT jsonb_agg(
          CASE WHEN children IS NULL
               THEN to_jsonb(sub) - 'children'
            -- THEN jsonb_build_object('title', title, 'item_id', item_id)  -- alt: spell out
               ELSE to_jsonb(sub) END
       )
FROM  (
   SELECT i.*, f_item_tree(i.item_id) AS children
   FROM   joins j
   JOIN   items i ON i.item_id = j.child_id
   WHERE  j.item_id = _item_id
   ORDER  BY i.item_id
   ) sub
$func$;

fiddle

Later, closely related answer with alternatives (most notably a maximum recursion level):

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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