MySQL json_search on numeric values

Viewed 2243

I've got a json list of objects like that

[{
    "something": "bla",
    "id": 2
}, {
    "something": "yes",
    "id": 1
}]

My id field is always a numeric value. But when I try to find id = 2, MySQL returns NULL

select
    json_search(
        json_extract(
            '[{"something": "bla" ,"id": 2}, {"something": "yes","id": 1}]',
            "$[*].id"
        ),
        'one',
        2
    ) as json_search;

json_search |
------------|
            |

When I use a string as value in my json id object instead of a numeric value, I got a result with Index 0.

select
    json_search(
        json_extract(
            '[{"something": "bla" ,"id": "2"}, {"something": "yes","id": 1}]',
            "$[*].id"
        ),
        'one',
        "2"
    ) as json_search;

json_search |
------------|
"$[0]"      |

I'm using MySQL 5.7.17

@@version  |
-----------|
5.7.17-log |

Is numeric search in json arrays not provided in MySQL?

2 Answers
Related