CODEWITHSUNDEEP
mysqlsqljson
demic
demic

Reputation: 65

JSON_Search() returns null

I have a problem with a query. In my DB I have a field that contains a JSON with this format:

[[0, 16, 22, 37, 0, 0, 0, 71, 82], 
[0, 18, 0, 36, 43, 0, 60, 0, 88], 
[9, 10, 0, 0, 0, 58, 69, 77, 0]]

With this query

SELECT JSON_SEARCH(NumeriJSON, 'all', 77,null, '$[*]') AS Indice FROM Cartella WHERE JSON_CONTAINS(NumeriJSON->'$[*]', '77')

I want to get the position of the number in the JSON, but it returns null. Why? The structure of JSON is valid, because JSON_CONTAINS works well. Thanks a lot.

Upvotes: 3

Views: 1158

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-search says:

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

    Returns the path to the given string within a JSON document.

I emboldened the word string. JSON_SEARCH() doesn't work if the values in your JSON are integers.

Your example works if you make them strings:

insert into Cartella (NumeriJson) values (
  '[["0", "16", "22", "37", "0", "0", "0", "71", "82"],
    ["0", "18", "0", "36", "43", "0", "60", "0", "88"],
    ["9", "10", "0", "0", "0", "58", "69", "77", "0"]]');

mysql> select json_search(numerijson, 'all', '77', null, '$[*]') as `index`
    from cartella where json_contains(numerijson->'$[*]', '"77"');
+-----------+
| index     |
+-----------+
| "$[2][7]" |
+-----------+

This issue was reported as a bug in 2015, but it was closed by simply documenting that it only supports string searches.

Supporting non-strings was then reported as a new feature request in 2018. So far, there is no solution.

In the meantime, you must store integers as strings if you want to use JSON_SEARCH().

I would also recommend to stop using JSON, and instead store integers in normal rows and columns. Then searches for integers work just fine.

P.S. Please don't use the back-formation "indice." There is no such word.

Upvotes: 3

Related Questions