Postgres 使用 row_to_json 进行递归查询

使用Postgres的row_to_json函数可以方便地进行递归查询，以生成包含层级关系的JSON格式数据。这项功能在处理复杂的数据结构和嵌套关系时非常有用。本文将介绍如何使用row_to_json函数，并提供一个案例代码来演示其用法。

sql
WITH recursive recursive_query AS (
  SELECT
    id,
    name,
    parent_id
  FROM
    category
  WHERE
    id = 1 -- 递归查询的起始节点
  UNION ALL
  SELECT
    c.id,
    c.name,
    c.parent_id
  FROM
    category c
    INNER JOIN recursive_query rq ON c.parent_id = rq.id
)
SELECT
  row_to_json(recursive_query) AS json
FROM
  recursive_query;

sql
CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  parent_id INTEGER
);
INSERT INTO category (name, parent_id) VALUES
  ('电子产品', NULL),
  ('手机', 1),
  ('平板电脑', 1),
  ('苹果手机', 2),
  ('华为手机', 2),
  ('iPad', 3),
  ('小米平板', 3);

sql
WITH recursive recursive_query AS (
  SELECT
    id,
    name,
    parent_id
  FROM
    category
  WHERE
    id = 1 -- 递归查询的起始节点
  UNION ALL
  SELECT
    c.id,
    c.name,
    c.parent_id
  FROM
    category c
    INNER JOIN recursive_query rq ON c.parent_id = rq.id
)
SELECT
  row_to_json(recursive_query) AS json
FROM
  recursive_query;

json
{
  "id": 1,
  "name": "电子产品",
  "parent_id": null,
  "children": [
    {
      "id": 2,
      "name": "手机",
      "parent_id": 1,
      "children": [
        {
          "id": 4,
          "name": "苹果手机",
          "parent_id": 2,
          "children": []
        },
        {
          "id": 5,
          "name": "华为手机",
          "parent_id": 2,
          "children": []
        }
      ]
    },
    {
      "id": 3,
      "name": "平板电脑",
      "parent_id": 1,
      "children": [
        {
          "id": 6,
          "name": "iPad",
          "parent_id": 3,
          "children": []
        },
        {
          "id": 7,
          "name": "小米平板",
          "parent_id": 3,
          "children": []
        }
      ]
    }
  ]
}