Как объединить таблицу с древовидной структурой в один вложенный объект JSON?

Это сложно. (Если я не упустил что-то важное, в чем я сомневаюсь.)

Это рекурсивная проблема, но стандартная рекурсивная CTE плохо справляются с этим, поскольку нам нужно агрегировать на всех уровнях, а CTE не допускают агрегирования в рекурсивном члене.

Я решил это с помощью функции PL / pgSQL:

CREATE OR REPLACE FUNCTION 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 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 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$;

Звоните (точно возвращает желаемый результат):

SELECT jsonb_pretty(f_build_jsonb_tree());

db ‹› fiddle здесь — с расширенным тестовым примером

Я выбрал имя ключа children вместо child, так как несколько могут быть вложенными.

jsonb_pretty() для улучшения отображения по желанию.

Это предполагает ссылочную целостность; должен быть реализован с ограничением FK.

Решение может быть проще для вашего конкретного случая, если использовать столбец code — если он демонстрирует (нераскрытые) полезные свойства. Как мы могли бы получить уровень вложенности без rCTE и добавить временную таблицу t. Но я стремлюсь к общему решению, основанному только на идентификаторах.

В функции много чего происходит. Я добавил встроенные комментарии. По сути, он делает это:

1. Создать временную таблицу с добавленным уровнем вложенности (lvl)
2. Если вложения не найдено, вернуть простой результат
3. Если вложенность обнаружена, сверните ее до jsonb с верхнего уровня вложенности вниз.
   Запишите все промежуточные результаты во вторую временную таблицу j.
4. Как только мы достигнем второго уровня вложенности, вернем полный результат.

Функция принимает _type в качестве параметра, чтобы вернуть только данный тип. В противном случае обрабатывается вся таблица.

В сторону: по возможности избегайте идентификаторов со смешанным регистром, таких как "parentId" в Postgres. Видеть:

• Учитываются ли имена столбцов PostgreSQL с учетом регистра?

Связанный более поздний ответ с использованием рекурсивной функции:

• Как превратить набор плоских деревьев в одно дерево с несколькими листьями?

Хороший. В какой-то момент я думал о временных таблицах, но остановился на этом единственном требовании запроса. — person DreaMy; 09.06.2020