Searching within nested JSON entries in MYSQL

Question

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?

Answer 1

$.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')

fiddle