从 Postgres 和 jOOQ 中的分层表递归生成 JSON 树

我在 Postgres 数据库中有一个分层表，例如category。结构很简单，如下所示：

我需要从这个表中得到的是递归的深层树结构，如下所示：

[
  {
    "id": 1,
    "name": "A",
    "children": [
      {
        "id": 3,
        "name": "A1",
        "children": [
          {
            "id": 4,
            "name": "A1a",
            "children": []
          },
          {
            "id": 5,
            "name": "A1b",
            "children": []
          }
        ]
      }
    ]
  },
  {
    "id": 2,
    "name": "B",
    "children": [
      {
        "id": 6,
        "name": "B1",
        "children": []
      },
      {
        "id": 7,
        "name": "B2",
        "children": []
      }
    ]
  },
]

是否有可能在未知深度下使用组合WITH RECURSIVE and json_build_array()或者其他解决方案？

使用 SQL 执行此操作

我在中找到了这个问题的答案这篇优秀的博客文章在这里 https://schinckel.net/2017/07/01/tree-data-as-a-nested-list-redux，因为我想知道如何在 jOOQ 中概括这个问题。帖子中的解决方案有一个缺陷，我已经在这篇博文中在我自己的版本中修复了这个问题 https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq.

如果 jOOQ 能够以通用方式具体化任意递归对象树，那将会很有用：https://github.com/jOOQ/jOOQ/issues/12341 https://github.com/jOOQ/jOOQ/issues/12341

同时，使用此 SQL 语句，该语句受到上述博客文章的启发，并进行了一些修改。如果必须的话，请转换为 jOOQ，不过您也可以将其存储为视图：

WITH RECURSIVE
  d1 (id, parent_id, name) as (
    values
      (1, null, 'A'),
      (2, null, 'B'),
      (3,    1, 'A1'),
      (4,    3, 'A1a'),
      (5,    3, 'A1b'),
      (6,    2, 'B1'),
      (7,    2, 'B2')
  ),
  d2 AS (
    SELECT d1.*, 0 AS level
    FROM d1
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d1.*, d2.level + 1
    FROM d1
    JOIN d2 ON d2.id = d1.parent_id
  ),
  d3 AS (
    SELECT d2.*, null::jsonb children
    FROM d2
    WHERE level = (SELECT max(level) FROM d2)
    UNION (
      SELECT
        (branch_parent).*, 
        jsonb_strip_nulls(
          jsonb_agg(branch_child - 'parent_id' - 'level'
            ORDER BY branch_child->>'name'
          ) FILTER (
            WHERE branch_child->>'parent_id' = (branch_parent).id::text
          )
        )
      FROM (
        SELECT
          branch_parent,
          to_jsonb(branch_child) AS branch_child
        FROM d2 branch_parent
        JOIN d3 branch_child ON branch_child.level = branch_parent.level + 1
      ) branch
      GROUP BY branch_parent
    )
  )
SELECT
  jsonb_pretty(jsonb_agg(to_jsonb(d3) - 'parent_id' - 'level')) AS tree
FROM d3
WHERE level = 0;

dbfiddle https://dbfiddle.uk/mEFH_bNX。再次，阅读链接的博客文章 https://schinckel.net/2017/07/01/tree-data-as-a-nested-list-redux解释它是如何工作的

在客户端中执行此操作

也许，您实际上不必在 SQL 中具体化层次结构，但可以在客户端中实现。 jOOQ 3.19 将附带一个新的Collectors::intoHierarchy收集器（参见#12341 https://github.com/jOOQ/jOOQ/issues/12341），它可以为您做到这一点：

record Category(int id, String name, List<Category> children) {}

// Optionally, make this a recursive query using WITH RECURSIVE or
// CONNECT BY, if you need to start from some node and then fetch
// only its children
List<Category> roots =
ctx.select(CATEGORY.ID, CATEGORY.PARENT_ID, CATEGORY.NAME)
   .from(CATEGORY)
   .orderBy(CATEGORY.ID)
   .collect(intoHierarchy(
       r -> r.value1(),
       r -> r.value2(),
       (r, l) -> new Category(r.value1(), r.value3(), l)
   ));

The Collector就是这样：

public static final <K, E, R extends Record> Collector<R, ?, List<E>> 
intoHierarchy(
    Function<? super R, ? extends K> keyMapper,
    Function<? super R, ? extends K> parentKeyMapper,
    BiFunction<? super R, ? super List<E>, ? extends E> recordMapper
) {
    return intoHierarchy(
        keyMapper, parentKeyMapper, recordMapper, ArrayList::new
    );
}


public static final <K, E, C extends Collection<E>, R extends Record> 
Collector<R, ?, List<E>> intoHierarchy(
    Function<? super R, ? extends K> keyMapper,
    Function<? super R, ? extends K> parentKeyMapper,
    BiFunction<? super R, ? super C, ? extends E> recordMapper,
    Supplier<? extends C> collectionFactory
) {
    record Tuple3<T1, T2, T3>(T1 t1, T2 t2, T3 t3) {}

    return collectingAndThen(
        intoMap(keyMapper, r -> {
            C e = collectionFactory.get();
            return new Tuple3<R, C, E>(r, e, recordMapper.apply(r, e));
        }),
        m -> {
            List<E> r = new ArrayList<>();

            m.forEach((k, v) -> {
                K parent = parentKeyMapper.apply(v.t1());
                E child = v.t3();

                if (m.containsKey(parent))
                    m.get(parent).t2().add(child);
                else
                    r.add(child);
            });

            return r;
        }
    );
}

现在，您可以使用任何方法将此树结构转换为 JSON 文档，例如使用杰克逊.