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"]');

Records: 3  Duplicates: 0  Warnings: 0


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


TRUNCATE tableA
INSERT INTO tableA VALUES 
  (1, '["mike","john"]'), 
  (2, '["mary","susan"]'), 
  (3, '["mark"]'),
  (4, '["bob","tom","kelly","tim"]');

Records: 4  Duplicates: 0  Warnings: 0


SELECT
  t1.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(t1.user, ']', ''), '[', ''), '"', ''), ',', arrays.a1), ',', -1) user
FROM
  (SELECT 1 a1 UNION ALL
   SELECT 2 UNION ALL 
   SELECT 3 UNION ALL
   SELECT 4 UNION ALL 
   SELECT 5) arrays INNER JOIN tableA t1
   ON CHAR_LENGTH(t1.user)-CHAR_LENGTH(REPLACE(t1.user, ',', ''))>=arrays.a1-1
ORDER BY id, a1


id
user


1
mike

1
john

2
mary

2
susan

3
mark

4
bob

4
tom

4
kelly

4
tim