-2

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

Adan_004
  • 1
  • 5

1 Answers1

0

Parsing out two array values:

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.

Parsing out more than two array values:

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.

griv
  • 2,098
  • 2
  • 12
  • 15
  • Thank you, it works only for an array with 2 users and if I want to get data from 3 users array it doesn't work – Adan_004 Sep 02 '22 at 22:02
  • Updated my Answer. Please update your question to reflect that you'd like to get more than two values from the arrays, my first answer was in response to your above example showing only two values. Thanks. – griv Sep 03 '22 at 02:45