query JSON array column in mysql into rows-CodePudding

I have a table with data like

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

And I'd like to extract the data from arrays into a more normalized form like

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

It seems like if this were possible, it would be with JSON_EXTRACT but I don't see any documentation on whether it is possible to output one row per path expression result, such that other non-JSON columns are output beside the path result.

CodePudding user response：

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 |
 --------- ---------