6

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  |
+-----+
Bill Karwin
  • 499,602
  • 82
  • 638
  • 795
Jibeji
  • 377
  • 3
  • 8
  • Does this answer your question? [Convert JSON array in MySQL to rows](https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows) – Nico Haase Feb 16 '20 at 14:50
  • Unfortunately not, as my column is a simple array, not a JSON with an index – Jibeji Feb 16 '20 at 14:54
  • please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad there is a possibilty to split that cmma seperated string you can find some examples here in SO – nbk Feb 16 '20 at 15:10
  • I'm curious, why did you store these values in a JSON array, when you want each value to be on its own row? – Bill Karwin Feb 16 '20 at 16:56
  • Just dealing with an existing database model :( – Jibeji Feb 16 '20 at 18:43

1 Answers1

8

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.

Bill Karwin
  • 499,602
  • 82
  • 638
  • 795