MySQL json_search by numeric values
I have a list of json objects like
[{
"something": "bla",
"id": 2
}, {
"something": "yes",
"id": 1
}]
The My field is id
always a numeric value. But when I try to find id = 2
, MySQL returnsNULL
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 a value in my json object id
instead of a numeric value, I got the result at 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 am using MySQL 5.7.17
@@version |
-----------|
5.7.17-log |
Is numeric search in json arrays not provided in MySQL?
source to share
Although the function was JSON_EXTRACT
returning [2, 1]
and it was valid JSON, if you search the documentation for the JSON_SEARCH
function:
JSON_SEARCH (json_doc, one_or_all, search_str [, escape_char [, path] ...])
So, as I understood, you can only work with values STRING
, not numeric values. But one solution to your problem might be to use a function JSON_CONTAINS
as it returns 1 or 0 if the numeric value exists or not.
select
json_contains(
json_extract(
'[{"something": "bla" ,"id": 2}, {"something": "yes","id": 1}]',
"$[*].id"
),
"2"
) as json_contains;
The only problem is that you couldn't get the path to the given value in the JSON document. Hope it helped.
source to share
You can try something tricky, non-intuitive, and possibly performance issues, but this is an option:
mysql> SELECT JSON_SEARCH(
-> REPLACE(
-> REPLACE(
-> REPLACE(
-> JSON_EXTRACT('[
'> {"something": "bla" ,"id": 2},
'> {"something": "yes","id": 1}
'> ]', "$[*].id"),
-> ', ', '","'),
-> '[', '["'),
-> ']', '"]'),
-> 'one', '2') `json_search`;
+-------------+
| json_search |
+-------------+
| "$[0]" |
+-------------+
1 row in set (0.00 sec)
source to share