Postgres recursive query with row_to_json, David's first attempt, 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,'1', ''),
(2,1,'1.1', ''),
(3,NULL,'2', ''),
(4,NULL,'3', ''),
(5,4,'3.1', ''),
(6,2,'1.1.1', '');

WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS (
  -- tree leaves (no matching children)
  SELECT c.*, json '[]'
  FROM customer_area_node c
  WHERE NOT EXISTS(SELECT * FROM customer_area_node AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)

UNION ALL

-- pozs's awesome "little hack"
  SELECT (parent).*, json_agg(child) AS "children"
  FROM (
    SELECT parent, child
    FROM customer_area_tree AS child
    JOIN customer_area_node parent ON parent.id = child.parent_id
  ) branch
  GROUP BY branch.parent
)
SELECT jsonb_pretty(json_agg(t)::jsonb)
FROM customer_area_tree t
LEFT JOIN customer_area_node AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id IS NULL;


absolute service time: 0,4 sec