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

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

Postgres recursive query with row_to_json, David's second 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, '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'),
  (9,  6,    'name9',  '1.3.6.9'),
  (10, 3,    'name10', '1.3.10');

WITH RECURSIVE
c_with_level AS (
    
    SELECT *, 0 as lvl
    FROM   customer_area_node
    WHERE  parent_id IS NULL

UNION ALL
    
    SELECT child.*, parent.lvl + 1
    FROM   customer_area_node child
    JOIN   c_with_level parent ON parent.id = child.parent_id
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c_with_level
),
c_tree AS (
    SELECT c_with_level.*, jsonb '[]' children
    FROM   c_with_level, maxlvl
    WHERE  lvl = maxlvl

UNION 
    (
        SELECT (branch_parent).*, jsonb_agg(branch_child)
        FROM (
            SELECT branch_parent, branch_child
            FROM c_with_level branch_parent
            JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id
        ) branch
        GROUP BY branch.branch_parent
            
        UNION
            
        SELECT c.*, jsonb '[]' children
        FROM   c_with_level c
        WHERE  NOT EXISTS (SELECT 1 FROM c_with_level hypothetical_child WHERE hypothetical_child.parent_id = c.id)
    )
)
SELECT jsonb_pretty(row_to_json(c_tree)::jsonb)
FROM c_tree
WHERE lvl = 0;


absolute service time: 0,44 sec

edit mode | history

  
jsonb_pretty

1
{
    "id": 1,
    "lvl": 0,
    "name": "name1",
    "children": [
        {
            "id": 3,
            "lvl": 1,
            "name": "name3",
            "children": [
                {
                    "id": 9,
                    "lvl": 2,
                    "name": "name10",
                    "children": [
                    ],
                    "parent_id": 3,
                    "customer_id": 10,
                    "description": "1.3.10"
                },
                {
                    "id": 7,
                    "lvl": 2,
                    "name": "name7",
                    "children": [
                    ],
                    "parent_id": 3,
                    "customer_id": 7,
                    "description": "1.3.7"
                },
                {
                    "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"
                }
            ],
            "parent_id": 1,
            "customer_id": 3,
            "description": "1.3"
        },
        {
            "id": 2,
            "lvl": 1,
            "name": "name2",
            "children": [
                {
                    "id": 5,
                    "lvl": 2,
                    "name": "name5",
                    "children": [
                    ],
                    "parent_id": 2,
                    "customer_id": 5,
                    "description": "1.2.5"
                },
                {
                    "id": 4,
                    "lvl": 2,
                    "name": "name4",
                    "children": [
                    ],
                    "parent_id": 2,
                    "customer_id": 4,
                    "description": "1.2.4"
                }
            ],
            "parent_id": 1,
            "customer_id": 2,
            "description": "1.2"
        }
    ],
    "parent_id": null,
    "customer_id": 1,
    "description": "1"
}

λ

                                      .NET NoSQL database for rapid development