MySQL joins with JSON array data?

Asked Mar 18 '17 at 20:50

Active Feb 04 '21 at 18:31

Viewed 281 times

I am trying to join data between two tables. But the data is stored using what appears to be JSON array data. Here is an example:

ID  name                types
45  Four O'Clock        {"1":"9","2":"10"}
46  Globe Amaranth      ["8","9"]

The second complication is the data is stored in two ways. With brackets and with braces. When it uses braces the first item in the key:value is the order and not relevant to my needs. I only need the second item which is the ID in the related table.

Is there anyway to elegantly perform a join with this data structure in SQL?

Thanks, -Matt

asked Mar 18 '17 at 20:50

Matthew Briney

What [version of MySQL](http://stackoverflow.com/q/8987679/2298301) is it please? If it's 5.7 or higher, then it may be possible to use MySQL's `JSON_EXTRACT` function – Dhruv Saxena Mar 18 '17 at 20:57
5.6.35 but I could upgrade to 5.7 if this is easier with that version. – Matthew Briney Mar 18 '17 at 22:07

MySQL joins with JSON array data?

0 Answers0