Как объединить таблицу с древовидной структурой в один вложенный объект 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

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