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.