1

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

Improve this question
2
  • What version of MySQL is it please? If it's 5.7 or higher, then it may be possible to use MySQL's JSON_EXTRACT function Commented Mar 18, 2017 at 20:57
  • 5.6.35 but I could upgrade to 5.7 if this is easier with that version. Commented Mar 18, 2017 at 22:07
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.