如何通过pid和path字段查询完整的JSON树形结构数据？

现有表结构及数据 #

CREATE TABLE t_city(
  id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) NOT NULL,
  path varchar(255) NOT NULL,
  leaf BOOLEAN NOT NULL,
  pid INT NOT NULL,
  level INT NOT NULL,
  sort numeric(12,8)
);
CREATE UNIQUE INDEX "unique_title" ON t_city(pid, name);
INSERT INTO t_city VALUES (1, 'New York State', '1', 'f', 0, 1, 2.00223000);
INSERT INTO t_city VALUES (2, 'New York City', '1,2', 'f', 1, 2, 2.00000000);
INSERT INTO t_city VALUES (3, 'Albany', '1,3', 'f', 1, 2, 1.04560000);
INSERT INTO t_city VALUES (6, 'Manhattan', '1,2,6', 'f', 2, 3, 5.00000000);
INSERT INTO t_city VALUES (7, 'Queens', '1,2,7', 'f', 2, 3, 1.00000000);
INSERT INTO t_city VALUES (4, 'The Bronx', '1.2,4', 'f', 2, 3, 12.00000000);
INSERT INTO t_city VALUES (5, 'Brooklyn', '1,2,5', 'f', 2, 3, 9.00000000);
INSERT INTO t_city VALUES (8, 'Staten Island', '1,2,8', 'f', 2, 3, 3.00000000);

期望生成的JSON树形结构 #

[
  {
    "id": 1,
    "name": "New York State",
    "path": "1",
    "leaf": false,
    "pid": 0,
    "level": 1,
    "sort": 2.00223000,
    "children": [
      {
        "id": 3,
        "name": "Albany",
        "path": "1,3",
        "leaf": true,
        "pid": 1,
        "level": 2,
        "sort": 1.04560000
      },
      {
        "id": 2,
        "name": "New York City",
        "path": "1,2",
        "leaf": false,
        "pid": 1,
        "level": 2,
        "sort": 2.00000000,
        "children": [
          {
            "id": 7,
            "name": "Queens",
            "path": "1,2,7",
            "leaf": true,
            "pid": 2,
            "level": 3,
            "sort": 1.00000000
          },
          {
            "id": 8,
            "name": "Staten Island",
            "path": "1,2,8",
            "leaf": true,
            "pid": 2,
            "level": 3,
            "sort": 3.00000000
          },
          {
            "id": 6,
            "name": "Manhattan",
            "path": "1,2,6",
            "leaf": true,
            "pid": 2,
            "level": 3,
            "sort": 5.00000000
          },
          {
            "id": 5,
            "name": "Brooklyn",
            "path": "1,2,5",
            "leaf": true,
            "pid": 2,
            "level": 3,
            "sort": 9.00000000
          },
          {
            "id": 4,
            "name": "The Bronx",
            "path": "1.2,4",
            "leaf": true,
            "pid": 2,
            "level": 3,
            "sort": 12.00000000
          }
        ]
      }
    ]
  }
]

查询要求 #

• 支持通过pid或path字段进行查询
• 同层级节点需按sort字段升序排序
• 尽量通用适配包含path、pid字段的表，无需手动指定结果集字段
• 允许使用递归查询实现，也可添加字段优化查询性能

内容的提问来源于stack exchange，提问作者SageJustus