I've got a mysql table that has a json entry (called data
) for horse racing, I've simplified a sample entry here:
{
"age": "4",
"course": "Ascot",
"horses": [
{
"number": "6",
},
{
"number": "5",
},
{
"number": "2",
},
{
"number": "7",
},
{
"number": "9",
}
],
"id_race": "242723",
"canceled": "0",
"distance": "5f",
"finished": "0",
"finish_time": ""
}
The following works fine if I want the races that have ID 242723:
SELECT * FROM races WHERE json_unquote(data->'$.id_race') = 242723;
But I'm actually trying to select the entries with horses number 2. The following just returns nothing:
SELECT * FROM races WHERE json_extract(data,'$.horses.number') = 2
And neither does this:
SELECT * FROM races WHERE json_unquote(data->'$.horses.number') = 2;
I've really tried looking it up but nothing is working for me. What am I doing wrong?
$.horses.number
tries to look up a number attribute in a horses object; you need to search your horses array using json_contains:
select id
from races
where json_contains(data, '{"number":"2"}', '$.horses')