Postgres recursive query with row_to_json, David's first attempt, PostgreSQL - rextester

Run Code | API | Code Wall | Misc | Feedback | Login | Theme | Privacy | Patreon

Postgres recursive query with row_to_json, David's first attempt

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

latest | history

  
json_tree

1
{
    "id": 1,
    "lvl": 0,
    "name": "name1",
    "children": [
        {
            "id": 3,
            "lvl": 1,
            "name": "name3",
            "children": [
                {
                    "id": 6,
                    "lvl": 2,
                    "name": "name6",
                    "children": [
                        {
                            "id": 8,
                            "lvl": 3,
                            "name": "name9",
                            "children": [
                            ],
                            "parent_id": 6,
                            "customer_id": 9,
                            "description": "1.3.6.9"
                        }
                    ],
                    "parent_id": 3,
                    "customer_id": 6,
                    "description": "1.3.6"
                },
                {
                    "id": 7,
                    "lvl": 2,
                    "name": "name7",
                    "children": [
                    ],
                    "parent_id": 3,
                    "customer_id": 7,
                    "description": "1.3.7"
                },
                {
                    "id": 9,
                    "lvl": 2,
                    "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"
}

λ

                                      .NET NoSQL database for rapid development