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

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"

Which version of mysql are you using ?

executable
– executable

2023-01-18 09:59:18 +00:00
Commented Jan 18, 2023 at 9:59 — executable
– executable, Commented Jan 18, 2023 at 9:59

gotqn · Accepted Answer · 2023-01-18 10:00:39Z

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;

answered Jan 18, 2023 at 10:00

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related