How make recursive json on mysql
MySQL 8+

Using MySQL 8, you can write a Recursive Common Table Expression.


Recursive Common Table Expression Examples


The example under Hierarchical Data Traversal is pretty much what you're looking for, what you call parent_id they call manager_id

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  -- Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      -- John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT *
FROM employee_paths
ORDER BY path;

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+


The docs even explain it,


  How the CTE works:
  
  
  The nonrecursive SELECT produces the row for the CEO (the row with a
  NULL manager ID).
  The path column is widened to CHAR(200) to ensure that there is room
  for the longer path values produced by the recursive SELECT.
  Each row produced by the recursive SELECT finds all employees who
  report directly to an employee produced by a previous row. For each
  such employee, the row includes the employee ID and name, and the
  employee management chain. The chain is the manager's chain, with the
  employee ID added to the end.
  Recursion ends when employees have no others who report to them.
  


They further have an example wuth specific employee or employees, for more information see "Hierarchical Data Traversal" with MySQL 8