Postgres LTREE display by grouping parents and children

Question

I have a sample Ltree Structure than I want to return it as JSON structure. I've tried searching on stack overflow but the results are giving wrong responses.

create table node
(
    id   integer not null,
    name varchar(255),
    path ltree   not null
);

and I have these data

INSERT INTO node (id,name,path) VALUES (1,'Residential','1');
INSERT INTO node (id,name,path) VALUES (2,'Commercial','2');
INSERT INTO node (id,name,path) VALUES (3,'Industrial','3');
INSERT INTO node (id,name,path) VALUES (4,'Res type 1','1.4');
INSERT INTO node (id,name,path) VALUES (5,'Comm type 1','2.5');
INSERT INTO node (id,name,path) VALUES (6,'Industrial 1','3.6');
INSERT INTO node (id,name,path) VALUES (7,'Residential 2','1.4.7');
INSERT INTO node (id,name,path) VALUES (8,'Commercial 2','2.5.8');
INSERT INTO node (id,name,path) VALUES (9,'Industrial 2','3.6.9');

and this is what I want to collect with my query

[
  {
    "name": "Residentioal",
    "children": [
      {
        "name": "Res type 1",
        "children": [
          {
            "name": "Residential 2",
            "children": []
          }
        ]
      }
    ]
  },
  {
    "name": "Commercial",
    "children": [
      {
        "name": "Comm type 1",
        "children": [
          {
            "name": "Commercial 2",
            "children": []
          }
        ]
      }
    ]
  },
  {
    "name": "Industrial",
    "children": [
      {
        "name": "Industrial 1",
        "children": [
          {
            "name": "Industrial 2",
            "children": []
          }
        ]
      }
    ]
  }
]

I tried recursive with .. but it keeps looping through without returning proper value.

Your ltree path do not fit your expected output. "commercial" is located under id 1 as well as "industrial 1". "industrial 2" is located under 3.6 which does not exist. I corrected the data in my example below. — S-Man, Jul 18 '19 at 08:18

S-Man · Accepted Answer · 2019-07-18T10:12:34.550

You need two parts, the recursion one and additionally a function. I explained this already here, here and here, so please have a look there for further explanations.

demo:db<>fiddle

Recursion

WITH RECURSIVE cte AS (
    SELECT 
        id,
        name,
        path,
        json_build_object('name', name, 'children', ARRAY[]::text[]) AS jsonobject,
        ARRAY[]::text[] || (row_number() OVER () - 1)::text as jsonpath,
        0 as depth        
    FROM node
    WHERE path = subpath(path, 0, 1) --parents

    UNION ALL

    SELECT
        n.id, 
        n.name, 
        n.path,
        json_build_object('name', n.name, 'children', ARRAY[]::text[]),
        jsonpath || '{children}' || (row_number() OVER (PARTITION BY subpath(n.path, depth, 1)::text ORDER BY subpath(n.path, depth + 1, 1)::text::int) - 1)::text,
        c.depth + 1
    FROM
        node n
    JOIN cte c 
    ON c.id = subpath(n.path, depth, 1)::text::int
       AND nlevel(n.path) = depth + 2 AND subpath(n.path, depth + 1, 1)::text::int = n.id
)
SELECT * FROM cte

The function

CREATE OR REPLACE FUNCTION nested_json() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN   
    _json_output := '[]'::jsonb;

    FOR _temprow IN
        -- <Add the CTE from above here>
    LOOP
        SELECT 
        jsonb_insert(
            _json_output, 
            _temprow.jsonpath, 
            _temprow.jsonobject
        )
        INTO _json_output;
    END LOOP;   

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;

Please notice: The ltree structure is not a really good choice for this use case because you need to calculate the subpaths again and again. A simple reference to the parent would be more helpful and faster.

Edit: The db<>fiddle admin is great and installed the ltree extension, so there is a new fiddle

I'm running Postgres 9.5 and JSONB_INSERT is only available from 9.6 onwards, any replacement to that ? — BardZH, Jul 18 '19 at 09:21
@Bardia jsonb_set works as well in this case https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=e1d665cfd9f6dc7355d2352c9b470f6c — S-Man, Jul 18 '19 at 09:24
I did this based on other example that you provided, but there is small issue with that, If I have two or more parents with children, it assigns all to the first parent [SQL](https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=006f7d607fd7698c1b8ae63ff3dd8b02) — BardZH, Jul 18 '19 at 09:39
@Bardia You are right. But with only one root as in your example it works fine https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=0a14f86fa4668cf5519f2f7ce52b50e9 — S-Man, Jul 18 '19 at 09:48
@Bardia if it works for you please do not forget to upvote (which honors the work and time the repliers invested into you problem) and accept (which shows that the problem has been solved). Otherwise please tell me which change is needed. — S-Man, Jul 18 '19 at 10:14
@Bardia: dbfiddle now works with ltree. Adjusted the fiddle (thanks to db<>fiddle admin!) — S-Man, Jul 18 '19 at 10:15

Postgres LTREE display by grouping parents and children

1 Answers1