binary_tree.sql

WITH RECURSIVE traversed (id, name, path, `left`, `right`) AS (
  SELECT id,
         name,
         CAST(JSON_ARRAY(id) AS JSON),
         `left`,
         `right`
  FROM binary_tree
  WHERE id = 1
  UNION
  SELECT b.id,
         b.name,
         JSON_ARRAY_APPEND(t.path, '$', b.id),
         b.`left`,
         b.`right`
  FROM traversed t
         LEFT JOIN binary_tree b
                   ON b.`id` = t.`left`
  WHERE t.`left` IS NOT NULL
  UNION
  SELECT b.id,
         b.name,
         JSON_ARRAY_APPEND(t.path, '$', b.id),
         b.`left`,
         b.`right`
  FROM traversed t
         LEFT JOIN binary_tree b
                   ON b.`id` = t.`right`
  WHERE t.`right` IS NOT NULL
)
SELECT json_length(path) AS depth,
       id,
       name,
       path,
       `left`,
       `right`
FROM traversed;

full_table.md

id	name	left	right
1	Ernestina Timer	2	5
2	Cida	3	4
3	Julia	NULL	NULL
4	Gabriela	6	NULL
5	Marinete	7	8
6	Ada	NULL	NULL
7	Janaina	NULL	NULL
8	Weverton	9	NULL
9	Gabriel	NULL	NULL

result.md

depth	id	name	path	left	right
1	1	Ernestina Timer	[1]	2	5
2	2	Cida	[1, 2]	3	4
2	5	Marinete	[1, 5]	7	8
3	3	Julia	[1, 2, 3]	NULL	NULL
3	7	Janaina	[1, 5, 7]	NULL	NULL
3	4	Gabriela	[1, 2, 4]	6	NULL
3	8	Weverton	[1, 5, 8]	9	NULL
4	6	Ada	[1, 2, 4, 6]	NULL	NULL
4	9	Gabriel	[1, 5, 8, 9]	NULL	NULL