MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tableA (id INT, user VARCHAR(500));
INSERT INTO tableA VALUES 
  (1, '["mike","john"]'), 
  (2, '["mary","susan"]'), 
  (3, '["mark"]');
SELECT * FROM tableA


id
user


1
["mike","john"]

2
["mary","susan"]

3
["mark"]


SELECT
    t1.id,
    REPLACE(REPLACE(REPLACE(t1.user, ']', ''), '[', ''), '"', '') AS user
FROM
(
    SELECT id, SUBSTRING_INDEX(user, ',', 1) AS user
    FROM tableA
    UNION
    SELECT id, SUBSTRING_INDEX(user, ',', -1) AS user
    FROM tableA
) t1
ORDER BY
    t1.id


id
user


1
mike

1
john

2
mary

2
susan

3
mark


SELECT * FROM tableA


id
user


1
["mike","john"]

2
["mary","susan"]

3
["mark"]

`id`	`user`
`1`	`["mike","john"]`
`2`	`["mary","susan"]`
`3`	`["mark"]`

`id`	`user`
`1`	`mike`
`1`	`john`
`2`	`mary`
`2`	`susan`
`3`	`mark`

`id`	`user`
`1`	`["mike","john"]`
`2`	`["mary","susan"]`
`3`	`["mark"]`