query JSON array column in mysql into rows

Question

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.

https://stackoverflow.blog/2022/06/02/a-beginners-guide-to-json-the-data-format-for-the-internet/?cb=1&_ga=2.147627319.1078370399.1654009084-2123733468.1626897298 — PM 77-1, Jun 02 '22 at 21:26
Are you using MySQL 8.0? The preferred way to do this is with [JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html). — Bill Karwin, Jun 02 '22 at 21:40
@PM77-1 Thanks for the helpful link. But I promise you that arrays are a valid JSON type, both in general and in mysql. In your browser console, try `JSON.stringify(["foo","bar"]);` — Anthony, Jun 03 '22 at 15:35
@BillKarwin Thanks! Looking at that documentation, I don't see how you could "join" based on just the column's placement within a row. Which is to say, I need it to be "user1 : cake; user1 : milk" but looking at JSON_TABLE, it seems like I can't treat that json table as a "member" of the given row. I'll keep reading. — Anthony, Jun 03 '22 at 15:46

score 1 · Accepted Answer · answered Jun 03 '22 at 15:50

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

answered Jun 03 '22 at 15:50

Bill Karwin

538,548
86
673
828

I keep hitting the same syntax error, and assumed it was because I didn't have the target json column as actual JSON type, but eventually realized it was because my local test env wasn't on mysql 8.0. Durp. Testing on 8.0 worked, but column was empty, turns out that was because field length needed to be longer. Once I straightened that out, this was what I needed. Thank you! – Anthony Jun 03 '22 at 17:06

query JSON array column in mysql into rows

1 Answers1