Hi everyone I have a table where a column has data inside of square brackets and I want to convert it to rows in MySql
example
I have this`
| id | user |
|---|---|
| 1 | ["mike","john"] |
| 2 | ["mary","susan"] |
| 3 | ["mark"] |
and with a query get this information
| id | user |
|---|---|
| 1 | mike |
| 1 | john |
| 2 | mary |
| 2 | susan |
| 3 | mark |
Is this possible
thank you in advance
With MySQL 5.7, you can use a subquery with a UNION to get the values of the array before and after the commas.
Then, REPLACE the brackets and quotes with blank spaces.
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
See Fiddle.
The real solution, however, is to change your database design. You should not store array values into a single column unless you have a specific reason.
If you do need to store it as an array, I'd suggest upgrading your MySQL version and store it in a JSON Data Type, available since MySQL 5.7.8.
If you'd like to handle more than two array values, you can use the following query:
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
See Fiddle.
Note, you will have to add more values to the UNION ALL subquery if you have more than 5 values in your array since it essentially creates a counter to retrieve x amount of strings in your array.
You can read more about the above query in the following Stack Overflow Discussion:
SQL split values to multiple rows
Another option would be to upgrade your version of MySQL to version 8 to utilize the JSON_TABLE() functions, available since MySQL 8.0.4.
