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

У меня есть таблица в базе данных Postgres 11.4 с самодостаточной древовидной структурой:

+------------+
| account    |  
+------------+
| id         |
| code       | 
| type       |
| parentId   | -- references account.id
+------------+

У каждого ребенка может быть еще один ребенок, уровень вложенности не ограничен.

Я хочу сгенерировать из него один объект JSON, вложив всех детей (ресурсивно).

Можно ли решить это одним запросом? Или любое другое решение, использующее typeORM с одной таблицей?
Иначе мне придется вручную привязать данные на стороне сервера.

Я пробовал этот запрос:

SELECT account.type, json_agg(account) as accounts
FROM account
-- LEFT JOIN "account" "child" ON "child"."parentId"="account"."id" -- tried to make one column child
GROUP BY account.type   

Результат:

[
  ...
  {
    "type": "type03",
    "accounts": [
      {
        "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
      }
    ]
  }
  ...
]

Вместо этого я ожидаю этого:

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

Может ли он иметь более высокий уровень вложенности (например, acc03.001.001.0001, родительский элемент будет acc03.001.001)? Может ли он иметь несколько корней в таблице (с parentId=null) для каждого типа?   —  person DreaMy    schedule 09.06.2020

Ваша версия Postgres (всегда)? Сколько уровней вложенности возможно?   —  person DreaMy    schedule 09.06.2020

да, у ребенка может быть еще одно гнездо. код просто пример, может быть случайным. К сожалению, parentId имеет значение null и вложенный дочерний элемент. я уже редактировал.   —  person DreaMy    schedule 09.06.2020

Так что это для более простой версии без многоуровневого вложения: «; с nested_accounts as (SELECT account.type, account.parentId, json_agg (account) as account from account group by type, parentId) select a.type, na. учетные записи из nested_accounts для внутреннего присоединения к учетной записи a.id = na.parentId; « Для мульти-вложенности, вероятно, должна быть добавлена ​​рекуррентность.   —  person DreaMy    schedule 09.06.2020

См. также:  nodejs и IoT: союз, заключенный на небесах

@ Адам: Да, верный путь. Но с некоторой рекуррентностью оказалось непросто.   —  person DreaMy    schedule 09.06.2020

Понравилась статья? Поделиться с друзьями:
IT Шеф
Комментарии: 1
  1. DreaMy

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

    Это рекурсивная проблема, но стандартная рекурсивная 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. Видеть:


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

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

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: