+---------+-----+----------------------------------------+
| user_id | roles                                        |
+---------+-----+----------------------------------------+
|       1 | ["Admin","Editor","Developer","Programmer"]  |
|       2 | ["Admin","Editor","Developer"]               |
+---------+-----+----------------------------------------+

SELECT
  user_id,
  idx,
  JSON_EXTRACT(roles, CONCAT('$[', idx, ']')) AS roles
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  SELECT  3 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  4
  ) AS indexes
WHERE JSON_EXTRACT(roles, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY user_id, idx;

+---------+-----+-------------+
| user_id | idx | roles       |
+---------+-----+-------------+
|       1 |   0 | Admin       |
|       1 |   1 | Editor      |
|       1 |   2 | Developer   |
|       1 |   3 | Programmer  |
|       2 |   0 | Admin       |
|       2 |   1 | Editor      |
|       2 |   2 | Developer   |
+---------+-----+-------------+