Postgres recursive query with row_to_json, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

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

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

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;


absolute service time: 0,42 sec