查询JSON数组中的列在mysql中的行

Question

我有一个数据表

| user_id   | favorite_foods                        |
|---------  |-------------------------------------- |
| user1     | ["milk","cake"]                       |
| user2     | null                                  |
| user3     | ["cake","hotdogs"]                    |
| user4     | ["cheese","apples","cake","hotdogs"]  |

我想把数组中的数据提取成更规范化的形式，比如

| user1     | milk      |
| user1     | cake      |
| user2     | null      |
| user3     | cake      |
| user3     | hotdogs   |
| user4     | cheese    |
| user4     | apples    |
| user4     | cake      |
| user4     | hotdogs   |

似乎如果这是可能的，它将与JSON_EXTRACT，但我没有看到任何文档是否有可能输出一行每个路径表达式结果，这样其他非json列在路径结果旁边输出。

Answer 1

select user_id, j.food from ihaveatablewithdatalike 
cross join json_table(favorite_foods, '$[*]' columns ( 
food varchar(20) path '$')) as j;
+---------+---------+
| user_id | food    |
+---------+---------+
| user1   | milk    |
| user1   | cake    |
| user3   | cake    |
| user3   | hotdogs |
| user4   | cheese  |
| user4   | apples  |
| user4   | cake    |
| user4   | hotdogs |
+---------+---------+