Postgres 13

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 account (
  id int PRIMARY KEY
, code text
, type text
, "parentId" int --> account.id
);

-- reverse-engineered original sample data
INSERT INTO account
SELECT *
FROM  json_populate_recordset(null::account, '[
      {
        "id": 28,
        "code": "acc03.001",
        "type": "type03",
        "parentId": null
      },
      {
        "id": 29,
        "code": "acc03.001.001",
        "type": "type03",
        "parentId": 28
      },
      {
        "id": 30,
        "code": "acc03.001.002",
        "type": "type03",
        "parentId": 28
      }
    ]'
);

-- additional test data
INSERT INTO account VALUES
  (10, 'bcc03.001'            , 'type04', NULL)
, (20, 'bcc03.001.001'        , 'type04', 10)
, (21, 'bcc03.001.002'        , 'type04', 10)
, (33, 'bcc03.001.001.001'    , 'type04', 20)
, (31, 'bcc03.001.001.002'    , 'type04', 20)
, (25, 'bcc03.001.002.001'    , 'type04', 21)
, (40, 'bcc03.001.001.001.001', 'type04', 33)
, (41, 'bcc03.001.001.001.002', 'type04', 33)

, (44, 'xcc03.001'            , 'type04', NULL)

, (66, 'ycc03.001'            , 'type06', NULL);

CREATE TABLE



INSERT 0 3



INSERT 0 10


TABLE account;


id
code
type
parentId


28
acc03.001
type03
null

29
acc03.001.001
type03
28

30
acc03.001.002
type03
28

10
bcc03.001
type04
null

20
bcc03.001.001
type04
10

21
bcc03.001.002
type04
10

33
bcc03.001.001.001
type04
20

31
bcc03.001.001.002
type04
20

25
bcc03.001.002.001
type04
21

40
bcc03.001.001.001.001
type04
33

41
bcc03.001.001.001.002
type04
33

44
xcc03.001
type04
null

66
ycc03.001
type06
null



SELECT 13


-- recursive plpgsql function
CREATE OR REPLACE FUNCTION pg_temp.f_build_jsonb_tree(_type text = NULL)
  RETURNS jsonb
  LANGUAGE plpgsql AS
$func$
DECLARE
   _nest_lvl int;

BEGIN
   -- add level of nesting recursively
   CREATE TEMP TABLE t ON COMMIT DROP AS
   WITH RECURSIVE t AS (
      SELECT *, 1 AS lvl
      FROM   account
      WHERE  "parentId" IS NULL
      AND   (type = _type OR _type IS NULL) -- default: whole table

      UNION ALL
      SELECT a.*, lvl + 1
      FROM   t
      JOIN   account a ON a."parentId" = t.id
      )
   TABLE t;
   
   -- optional idx for big tables with many levels of nesting
   -- CREATE INDEX ON t (lvl, id);

   _nest_lvl := (SELECT max(lvl) FROM t);

   -- no nesting found, return simple result
   IF _nest_lvl = 1 THEN 
      RETURN (  -- exits functions
      SELECT jsonb_agg(sub) -- AS result
      FROM  (
         SELECT type
              , jsonb_agg(sub) AS accounts
         FROM  (
            SELECT id, code, type, "parentId", NULL AS children
            FROM   t
            ORDER  BY type, id
            ) sub
         GROUP BY 1
         ) sub
      );
   END IF;

   -- start collapsing with leaves at highest level
   CREATE TEMP TABLE j ON COMMIT DROP AS
   SELECT "parentId" AS id
        , jsonb_agg (sub) AS children
   FROM  (
      SELECT id, code, type, "parentId"  -- type seems redundant!
      FROM   t
      WHERE  lvl = _nest_lvl
      ORDER  BY id
      ) sub
   GROUP  BY "parentId";

   -- optional idx for big tables with many levels of nesting
   -- CREATE INDEX ON j (id);

   -- iterate all the way down to lvl 2
   -- write to same table; ID is enough to identify
   WHILE _nest_lvl > 2
   LOOP
      _nest_lvl := _nest_lvl - 1;

      INSERT INTO j(id, children)
      SELECT "parentId"     -- AS id
           , jsonb_agg(sub) -- AS children
      FROM  (
         SELECT id, t.code, t.type, "parentId", j.children  -- type seems redundant!
         FROM   t
         LEFT   JOIN j USING (id)  -- may or may not have children
         WHERE  t.lvl = _nest_lvl
         ORDER  BY id
         ) sub
      GROUP  BY "parentId";
   END LOOP;

   -- nesting found, return nested result
   RETURN ( -- exits functions
   SELECT jsonb_agg(sub) -- AS result
   FROM  (
      SELECT type
           , jsonb_agg (sub) AS accounts
      FROM  (
         SELECT id, code, type, "parentId", j.children
         FROM   t
         LEFT   JOIN j USING (id)
         WHERE  t.lvl = 1
         ORDER  BY type, id
         ) sub
      GROUP  BY 1
      ) sub
   );
END
$func$;

CREATE FUNCTION


-- only for 'type03'
SELECT jsonb_pretty(pg_temp.f_build_jsonb_tree('type03'));


jsonb_pretty


[
    {
        "type": "type03",
        "accounts": [
            {
                "id": 28,
                "code": "acc03.001",
                "type": "type03",
                "children": [
                    {
                        "id": 29,
                        "code": "acc03.001.001",
                        "type": "type03",
                        "parentId": 28
                    },
                    {
                        "id": 30,
                        "code": "acc03.001.002",
                        "type": "type03",
                        "parentId": 28
                    }
                ],
                "parentId": null
            }
        ]
    }
]



SELECT 1


-- whole table
SELECT jsonb_pretty(pg_temp.f_build_jsonb_tree());


jsonb_pretty


[
    {
        "type": "type03",
        "accounts": [
            {
                "id": 28,
                "code": "acc03.001",
                "type": "type03",
                "children": [
                    {
                        "id": 29,
                        "code": "acc03.001.001",
                        "type": "type03",
                        "children": null,
                        "parentId": 28
                    },
                    {
                        "id": 30,
                        "code": "acc03.001.002",
                        "type": "type03",
                        "children": null,
                        "parentId": 28
                    }
                ],
                "parentId": null
            }
        ]
    },
    {
        "type": "type04",
        "accounts": [
            {
                "id": 10,
                "code": "bcc03.001",
                "type": "type04",
                "children": [
                    {
                        "id": 20,
                        "code": "bcc03.001.001",
                        "type": "type04",
                        "children": [
                            {
                                "id": 31,
                                "code": "bcc03.001.001.002",
                                "type": "type04",
                                "children": null,
                                "parentId": 20
                            },
                            {
                                "id": 33,
                                "code": "bcc03.001.001.001",
                                "type": "type04",
                                "children": [
                                    {
                                        "id": 40,
                                        "code": "bcc03.001.001.001.001",
                                        "type": "type04",
                                        "parentId": 33
                                    },
                                    {
                                        "id": 41,
                                        "code": "bcc03.001.001.001.002",
                                        "type": "type04",
                                        "parentId": 33
                                    }
                                ],
                                "parentId": 20
                            }
                        ],
                        "parentId": 10
                    },
                    {
                        "id": 21,
                        "code": "bcc03.001.002",
                        "type": "type04",
                        "children": [
                            {
                                "id": 25,
                                "code": "bcc03.001.002.001",
                                "type": "type04",
                                "children": null,
                                "parentId": 21
                            }
                        ],
                        "parentId": 10
                    }
                ],
                "parentId": null
            },
            {
                "id": 44,
                "code": "xcc03.001",
                "type": "type04",
                "children": null,
                "parentId": null
            }
        ]
    },
    {
        "type": "type06",
        "accounts": [
            {
                "id": 66,
                "code": "ycc03.001",
                "type": "type06",
                "children": null,
                "parentId": null
            }
        ]
    }
]



SELECT 1
id	code	type	parentId
28	acc03.001	type03	null
29	acc03.001.001	type03	28
30	acc03.001.002	type03	28
10	bcc03.001	type04	null
20	bcc03.001.001	type04	10
21	bcc03.001.002	type04	10
33	bcc03.001.001.001	type04	20
31	bcc03.001.001.002	type04	20
25	bcc03.001.002.001	type04	21
40	bcc03.001.001.001.001	type04	33
41	bcc03.001.001.001.002	type04	33
44	xcc03.001	type04	null
66	ycc03.001	type06	null