Note: This is for MySQL 5.7.
When I attempt to use JSON_SEARCH to find the path to a number value within a JSON array, I get NULL. But when I use JSON_SEARCH to look for a string, I actually get the path.
# String Search Example
SET @json = '[1, 2, 3, "abc"]';
SELECT JSON_SEARCH(@json, 'one', 'abc');
----------
| "$[3]" |
----------
...but when I explicitly search for a number value, I get NULL?
# Number Search Example
SET @json = '[1, 2, 3, "abc"]';
SELECT JSON_SEARCH(@json, 'one', 1);
----------
| NULL |
----------
What's strange is that JSON_CONTAINS still works as intended with numbers or strings.
解决方案
JSON_SEARCH function works with only string scalars as mentioned in the documentation : Functions That Search JSON Values
Here you can see: