时间戳

首页 > 解决方案 > 从 Postgres 和 jOOQ 中的分层表递归生成 JSON 树

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

问题描述

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

ID parent_id 姓名
1 无效的 一个
2 无效的 乙
3 1 A1
4 3 A1a
5 3 A1b
6 2 B1
7 2 B2

我需要从这张表中得到的是这样的递归深度树结构：

[
  {
    "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和json_build_array()或其他解决方案的组合以未知深度？

标签： jsonpostgresqljooq

解决方案

我在这里的这篇优秀的博客文章中找到了这个问题的答案，因为我想知道如何在 jOOQ 中概括这个问题。如果 jOOQ 可以以通用方式实现任意递归对象树，那将会很有用：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.*, jsonb_build_array() children
    FROM d2
    WHERE level = (SELECT max(level) FROM d2)
    UNION (
      SELECT (branch_parent).*, jsonb_agg(branch_child)
      FROM (
        SELECT 
          branch_parent, 
          to_jsonb(branch_child) - 'level' - 'parent_id' AS branch_child
        FROM d2 branch_parent
        JOIN d3 branch_child ON branch_child.parent_id = branch_parent.id
      ) branch
      GROUP BY branch.branch_parent
      UNION
      SELECT d2.*, jsonb_build_array()
      FROM d2
      WHERE d2.id NOT IN (
        SELECT parent_id FROM d2 WHERE parent_id IS NOT NULL
      )
    )
  )
SELECT jsonb_pretty(jsonb_agg(to_jsonb(d3) - 'level' - 'parent_id')) AS tree
FROM d3
WHERE level = 0;

数据库小提琴。再次阅读链接的博客文章，了解其工作原理

推荐阅读