MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `dep` (
  `id` int NOT NULL,
  `dep` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `parent` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (1,'org1',NULL);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (2,'org1.1',1);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (3,'org1.1.1 ',2);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (4,'org1.1.1.1',3);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (5,'org1.1.1.1.1',4);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (6,'org1.1.1.1.1.1',5);
INSERT INTO `dep` (`id`,`dep`,`parent`) VALUES (7,'org1.1.1.1.1.2',5);
SELECT * FROM dep;


id
dep
parent


1
org1
null

2
org1.1
1

3
org1.1.1
2

4
org1.1.1.1
3

5
org1.1.1.1.1
4

6
org1.1.1.1.1.1
5

7
org1.1.1.1.1.2
5


WITH RECURSIVE
cte1 AS ( SELECT *, 0 level
          FROM dep
          WHERE id = 4 -- начальный узел
          UNION ALL
          SELECT dep.*, cte1.level + 1
          FROM cte1
          JOIN dep ON cte1.id = dep.parent ),
cte2 AS ( SELECT JSON_OBJECTAGG( id, 
                                 JSON_OBJECT( 'id', id,
                                              'dep', dep,
                                              'depth', level,
                                              'parent', parent,
                                              'children', '[]' )) obj,
                 MAX(parent) parent,
                 level,
                 MAX(id) id
          FROM cte1
          GROUP BY level ),
cte3 AS ( ( SELECT id, level, obj, parent
            FROM cte2
            ORDER BY level DESC LIMIT 1 )
          UNION ALL
          SELECT cte2.id, 
                 cte2.level,
                 JSON_MERGE_PATCH(cte2.obj,
                                  JSON_OBJECT(cte2.id,
                                              JSON_OBJECT('children', cte3.obj))),
                 cte2.parent
          FROM cte3
          JOIN cte2 ON cte3.parent = cte2.id )
SELECT CAST(obj AS CHAR)
FROM cte3
WHERE level = 0;


CAST(obj AS CHAR)


{"4": {"id": 4, "dep": "org1.1.1.1", "depth": 0, "parent": 3, "children": {"5": {"id": 5, "dep": "org1.1.1.1.1", "depth": 1, "parent": 4, "children": {"6": {"id": 6, "dep": "org1.1.1.1.1.1", "depth": 2, "parent": 5, "children": "[]"}, "7": {"id": 7, "dep": "org1.1.1.1.1.2", "depth": 2, "parent": 5, "children": "[]"}}}}}}


WITH RECURSIVE
cte1 AS ( SELECT *, 0 level
          FROM dep
          WHERE id = 1
          UNION ALL
          SELECT dep.*, cte1.level + 1
          FROM cte1
          JOIN dep ON cte1.id = dep.parent ),
cte2 AS ( SELECT JSON_OBJECTAGG( id, 
                                 JSON_OBJECT( 'id', id,
                                              'dep', dep,
                                              'depth', level,
                                              'parent', parent,
                                              'children', '[]' )) obj,
                 MAX(parent) parent,
                 level,
                 MAX(id) id
          FROM cte1
          GROUP BY level ),
cte3 AS ( ( SELECT id, level, obj, parent
            FROM cte2
            ORDER BY level DESC LIMIT 1 )
          UNION ALL
          SELECT cte2.id, 
                 cte2.level,
                 JSON_MERGE_PATCH(cte2.obj,
                                  JSON_OBJECT(cte2.id,
                                              JSON_OBJECT('children', cte3.obj))),
                 cte2.parent
          FROM cte3
          JOIN cte2 ON cte3.parent = cte2.id )
SELECT CAST(obj AS CHAR)
FROM cte3
WHERE level = 0;


CAST(obj AS CHAR)


{"1": {"id": 1, "dep": "org1", "depth": 0, "parent": null, "children": {"2": {"id": 2, "dep": "org1.1", "depth": 1, "parent": 1, "children": {"3": {"id": 3, "dep": "org1.1.1 ", "depth": 2, "parent": 2, "children": {"4": {"id": 4, "dep": "org1.1.1.1", "depth": 3, "parent": 3, "children": {"5": {"id": 5, "dep": "org1.1.1.1.1", "depth": 4, "parent": 4, "children": {"6": {"id": 6, "dep": "org1.1.1.1.1.1", "depth": 5, "parent": 5, "children": "[]"}, "7": {"id": 7, "dep": "org1.1.1.1.1.2", "depth": 5, "parent": 5, "children": "[]"}}}}}}}}}}}}
id	dep	parent
1	org1	null
2	org1.1	1
3	org1.1.1	2
4	org1.1.1.1	3
5	org1.1.1.1.1	4
6	org1.1.1.1.1.1	5
7	org1.1.1.1.1.2	5