postgresql递归查询转JSON

1、创建数据表

CREATE TABLE customer_area_node
(
  id          bigserial NOT NULL PRIMARY KEY,
  customer_id integer   NOT NULL,
  parent_id   bigint,
  name        text,
  description text
);

2、插入记录

INSERT INTO customer_area_node(customer_id, parent_id, name, description) VALUES
  (1,  NULL, 'name1',  '1'),
  (2,  1,    'name2',  '1.2'),
  (3,  1,    'name3',  '1.3'),
  (4,  2,    'name4',  '1.2.4'),
  (5,  2,    'name5',  '1.2.5'),
  (6,  3,    'name6',  '1.3.6'),
  (7,  3,    'name7',  '1.3.7'),
  (8,  5,    'name8',  '1.2.5.8'),
  (9,  6,    'name9',  '1.3.6.9'),
  (10, 3,    'name10', '1.3.10');

3、递归查询语句

WITH RECURSIVE c AS (
    SELECT *, 0 as lvl
    FROM   customer_area_node
    WHERE  customer_id = 1 AND parent_id IS NULL
  UNION ALL
    SELECT customer_area_node.*, c.lvl + 1
    FROM   customer_area_node 
    JOIN   c ON customer_area_node.parent_id = c.id
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c
),
j AS (
    SELECT c.*, json '[]' children
    FROM   c, maxlvl
    WHERE  lvl = maxlvl
  UNION ALL
    SELECT   (c).*, array_to_json(array_agg(j) || array(SELECT r
                                                        FROM   (SELECT l.*, json '[]' children
                                                                FROM   c l, maxlvl
                                                                WHERE  l.parent_id = (c).id
                                                                AND    l.lvl < maxlvl
                                                                AND    NOT EXISTS (SELECT 1
                                                                                   FROM   c lp
                                                                                   WHERE  lp.parent_id = l.id)) r)) children
    FROM     (SELECT c, j
              FROM   c
              JOIN   j ON j.parent_id = c.id) v
    GROUP BY v.c
)
SELECT row_to_json(j) json_tree
FROM   j
WHERE  lvl = 0;

运行结果如下：

{"id":1,"customer_id":1,"parent_id":null,"name":"name1","description":"1","lvl":0,"children":[{"id":3,"customer_id":3,"parent_id":1,"name":"name3","description":"1.3","lvl":1,"children":[{"id":6,"customer_id":6,"parent_id":3,"name":"name6","description":"1.3.6","lvl":2,"children":[{"id":9,"customer_id":9,"parent_id":6,"name":"name9","description":"1.3.6.9","lvl":3,"children":[]}]},{"id":7,"customer_id":7,"parent_id":3,"name":"name7","description":"1.3.7","lvl":2,"children":[]},{"id":10,"customer_id":10,"parent_id":3,"name":"name10","description":"1.3.10","lvl":2,"children":[]}]},{"id":2,"customer_id":2,"parent_id":1,"name":"name2","description":"1.2","lvl":1,"children":[{"id":5,"customer_id":5,"parent_id":2,"name":"name5","description":"1.2.5","lvl":2,"children":[{"id":8,"customer_id":8,"parent_id":5,"name":"name8","description":"1.2.5.8","lvl":3,"children":[]}]},{"id":4,"customer_id":4,"parent_id":2,"name":"name4","description":"1.2.4","lvl":2,"children":[]}]}]}

数据量较大的情况下，递归查询较慢，所以要引入物化视图解决该问题。
4、创建物化视图
与建表或视图非常类似

CREATE MATERIALIZED VIEW IF NOT EXISTS view_customer_area_node AS
<上面的递归查询语句>

创建完成后直接通过以下语句进行查询

SELECT * FROM view_customer_area_node;

5、刷新物化视图
如果源表内容变更，必须通过以下语句手动刷新视图

REFRESH MATERIALIZE VIEW view_customer_area_node;

6、查看视图源码命令

select * from pg_matviews where matviewname = 'view_customer_area_node';

7、参考
Materialized views with PostgreSQL for beginners.