MySQL 5.7 : convert simple JSON_ARRAY to rows

Question

I have a simple table with a JSON_ARRAY column like that:

+----+---------+
| id | content |
+----+---------+
|  1 | [3, 4]  |
|  2 | [5, 6]  |
+----+---------+

I want to list all the content references for a specific id

SELECT JSON_EXTRACT(content, '$') as res FROM table WHERE id=1

But I'd like the result to be in rows:

+-----+
| res |
+-----+
|  3  |
|  4  |
+-----+

Answer 1

You can do this in MySQL 8.0 with JSON_TABLE():

select r.res from mytable, 
 json_table(mytable.content, '$[*]' columns (res int path '$')) r 
where mytable.id = 1

I tested on MySQL 8.0.17, and this is the output:

+------+
| res  |
+------+
|    3 |
|    4 |
+------+

If you use a version older than MySQL 8.0, you have these options:

• Find some impossibly complex SQL solution. This is almost always the wrong way to solve the problem, because you end up with code that is too expensive to maintain.
• Fetch the JSON array as-is, and explode it in application code.
• Normalize your data so you have one value per row, instead of using JSON arrays.

I often find questions on Stack Overflow about using JSON in MySQL that convince me that this feature has ruined MySQL. Developers keep using it inappropriately. They like that it makes it easy to insert semi-structured data, but they find that it makes querying that data far too complex.

---

Re comment from @ChetanOswal:

There are solutions for MySQL 5.7, but they are unsatisfying and my advice is to avoid them.

Demo:

We can start with the data shown in the original question above.

mysql> create table mytable (id serial primary key, content json);

mysql> insert into mytable values
    -> (1, '[3,4]'),
    -> (2, '[5,6]');

Next we need another table that simply has a series of integer values, at least as many as the longest JSON array.

mysql> create table numbers (number int primary key);

mysql> insert into numbers values (0), (1), (2);

Joining these tables using an inequality we can create as many rows as the number of items in the JSON arrays.

mysql> select * from mytable 
  join numbers on numbers.number < json_length(mytable.content);
+----+---------+--------+
| id | content | number |
+----+---------+--------+
|  1 | [3, 4]  |      0 |
|  1 | [3, 4]  |      1 |
|  2 | [5, 6]  |      0 |
|  2 | [5, 6]  |      1 |
+----+---------+--------+

Now we can use those numbers as array indexes to extract the values from the JSON arrays.

mysql> select *, 
  json_extract(mytable.content, concat('$[', numbers.number, ']')) as value 
 from mytable 
 join numbers on numbers.number < json_length(mytable.content);
+----+---------+--------+-------+
| id | content | number | value |
+----+---------+--------+-------+
|  1 | [3, 4]  |      0 | 3     |
|  1 | [3, 4]  |      1 | 4     |
|  2 | [5, 6]  |      0 | 5     |
|  2 | [5, 6]  |      1 | 6     |
+----+---------+--------+-------+

That's as far as I will advise on solving this in MySQL 5.7.

If you need features that are in MySQL 8.0, then you should upgrade to MySQL 8.0.

MySQL 5.7 is nearing its end of life in October 2023. It's time to upgrade anyway.

If you can't upgrade, and you don't like workaround solutions like I showed, then you should stop using SQL to work with JSON.