1

I have a TEXT variable in MySQL 5.7 that stores a JSON. Within this JSON there is a list with the following structure of N elements:

"brandedFares": [
{
"type": "name1",
"status": "allow"
},
{
"type": "name2",
"status": "not allowed"
},
...

The problem is that these elements may come in different order and some of those may be missing. I am building a summary table that can show if any of these elements come and their status.

name1_exists name1_status name2_exists name2_status
TRUE allow TRUE not allowed

I tried to work it out with JSON_EXTRACT and substring_index but the fact that the list does not have an order kind of throws me out. Do you have any ideas or advices of how to do this within MySQL?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
WillDCC
  • 11
  • 2
  • 3
    I don't think this is easy in MySQL. Don't use JSON if you need to perform queries like this, normalize your data. – Barmar Nov 16 '22 at 17:59
  • 1
    This is what JSON_TABLE() is for in MySQL 8.0. If you're still using MySQL 5.7, I agree with Barmar, don't store this data in JSON format (or the format you show above, which is not legal JSON). Instead, store the data in normal rows and columns. – Bill Karwin Nov 16 '22 at 18:15
  • *I have a TEXT variable in MySQL 5.7* What do you mean? local variable? user-defined variable? something else? provide SQL code which assigns shown value to the variable and desired output for this value. *how to do this within MySQL?* Does complete list of possible attribute names exists? – Akina Nov 16 '22 at 18:15
  • Thanks, we will migrate to MySQL 8.0 in a few weeks, so I'll wait to that and use JSON_TABLE instead. – WillDCC Nov 16 '22 at 19:22

0 Answers0