Postgres recursive query with row_to_json, PostgreSQL

Postgres recursive query with row_to_json

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'),
  (11, 1,    'name10', '1.11');

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,44 sec

jsonb_pretty

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