How to get values of an array into separate rows in MYSQL

Question

I have a table in which a columns has an Array.

id	data
1	["a", "b"]
2	["a", "b", "c"]

I am using a query that is given below.

select JSON_EXTRACT(t.date, '$') as id from table1 t where t.id = 1; 

This gives result as the complete array, if I change the parameter like '$[0]' then I get value at 0 index.

How can I get result as follow : i.e (all the array values in separate row)

result
"a"
"b"

Answer 1

Are you looking for this:

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO mytable (id, data) VALUES (1, '["a", "b"]');
INSERT INTO mytable (id, data) VALUES (2, '["a", "b", "c"]');

SELECT T.id
      ,data.value
FROM mytable T
INNER JOIN JSON_TABLE
(
  T.data,
  "$[*]"
  COLUMNS(
    Value varchar(50) PATH "$"
  )
) data;