MySQL json_search on numeric values

Question

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?

did you get a solution to query numeric values?

helloworld
– helloworld

2023-09-20 12:41:16 +00:00
Commented Sep 20, 2023 at 12:41 — helloworld
– helloworld, Commented Sep 20, 2023 at 12:41

Ricardo Faria · Accepted Answer · 2017-06-29 16:39:00Z

Although the JSON_EXTRACT function was returning [2, 1] and it was a valid JSON, if you search the documentation the JSON_SEARCH function is:

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

So, as I understood you can only work with STRING values, and not with numeric values. But one solution to your problem could be to use the JSON_CONTAINS function 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 could not get the path to the given value within the JSON document. Hope it helped.

But then I have to use a string value as search value...also not really satisfactory

wchiquito · Accepted Answer · 2017-06-29 18:03:00Z

2

You can try something complicated, not intuitive and possibly with performance problems, but it's 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)

edited Jun 29, 2017 at 18:03

answered Jun 29, 2017 at 17:07

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

1 Comment

Markus Over a year ago

Ok, that works. But indeed, it doesn't look intuitive and I'm afraid that it will be slow with real datas.

Collectives™ on Stack Overflow

MySQL json_search on numeric values

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related