Sample Header Ad - 728x90

Create JSON object from recursive tree structure

5 votes
2 answers
8922 views
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)
;
*dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a7a2ddc8d5c8f4053eed201ef359753e)* 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.
Asked by Ben (179 rep)
Jan 17, 2018, 03:51 PM
Last activity: Nov 20, 2023, 11:18 AM