Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

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

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


WITH RECURSIVE cte AS (
    SELECT 
        id,
        name,
        path,
        jsonb_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,
        jsonb_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


id
name
path
jsonobject
jsonpath
depth


1
Residential
1
{"name": "Residential", "children": []}
{0}
0

2
Commercial
2
{"name": "Commercial", "children": []}
{1}
0

3
Industrial
3
{"name": "Industrial", "children": []}
{2}
0

4
Res type 1
1.4
{"name": "Res type 1", "children": []}
{0,children,0}
1

5
Comm type 1
2.5
{"name": "Comm type 1", "children": []}
{1,children,0}
1

6
Industrial 1
3.6
{"name": "Industrial 1", "children": []}
{2,children,0}
1

7
Residential 2
1.4.7
{"name": "Residential 2", "children": []}
{0,children,0,children,0}
2

8
Commercial 2
2.5.8
{"name": "Commercial 2", "children": []}
{1,children,0,children,0}
2

9
Industrial 2
3.6.9
{"name": "Industrial 2", "children": []}
{2,children,0,children,0}
2



SELECT 9


CREATE OR REPLACE FUNCTION nested_json() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN	
    _json_output := '[]'::jsonb;
  
    FOR _temprow IN
	WITH RECURSIVE cte AS (
            SELECT 
                id,
                name,
                path,
                jsonb_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,
                jsonb_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
   LOOP
        SELECT 
		jsonb_insert(
			_json_output, 
			_temprow.jsonpath, 
			_temprow.jsonobject
		)
		INTO _json_output;
    END LOOP;   
   
    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION


SELECT * FROM nested_json()


nested_json


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



SELECT 1

id	name	path	jsonobject	jsonpath	depth
1	Residential	1	{"name": "Residential", "children": []}	{0}	0
2	Commercial	2	{"name": "Commercial", "children": []}	{1}	0
3	Industrial	3	{"name": "Industrial", "children": []}	{2}	0
4	Res type 1	1.4	{"name": "Res type 1", "children": []}	{0,children,0}	1
5	Comm type 1	2.5	{"name": "Comm type 1", "children": []}	{1,children,0}	1
6	Industrial 1	3.6	{"name": "Industrial 1", "children": []}	{2,children,0}	1
7	Residential 2	1.4.7	{"name": "Residential 2", "children": []}	{0,children,0,children,0}	2
8	Commercial 2	2.5.8	{"name": "Commercial 2", "children": []}	{1,children,0,children,0}	2
9	Industrial 2	3.6.9	{"name": "Industrial 2", "children": []}	{2,children,0,children,0}	2