Table

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);

I want to query to get the following json tree structure data:

[
  {
    "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
          }
        ]
      }
    ]
  }
]

This tree can be obtained through the pid or path field value. Sort nodes of the same level by the sort field.

I would have thought this was a common tree structure, but all I found in stack overflow were examples of tree structures that are unique.

As far as possible, queries for tables with path, pid fields can be used, rather than needing to specify the table’s fields in the result set.

Recursion and extension can be used.

If it is possible to improve query performance, you can add fields.

Thanks.