Performing wildcard search using the json_contains function in mysql 5.7 and above

Question

I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:-

select json_object(..) from tblxyz 
  where json_contains(columnname, '{"MyName" : "FirstName LastName"}')

The problem with this approach is that I require the entire string "FirstName LastName" to find this row in the actual result. Now I need to perform a wild card search. That is:-

select json_object(..) from tblxyz 
  where json_contains(columnname, '{"MyName" : "FirstName"}')

should also return this entry to me in the final result. Can someone please suggest any workaround?

Answer 1

You can use JSON_SEARCH with wildcards:

SET @j = '{"MyName": "FirstName LastName"}';
SELECT JSON_SEARCH(@j, 'all', 'FirstName%', NULL, '$.MyName');

Will return the path: "$.MyName"

If you want to filter by this, use an alias and check it in a HAVING clause:

SELECT JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') as `search`
FROM `users`
HAVING `search` IS NOT NULL;

or.. in a WHERE:

SELECT *
FROM `users`
WHERE JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') IS NOT NULL;

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

Answer 2

Sorry, but you are really expecting too much from JSON in a database.

Any JSON "fields" that need to be searched/sorted/joined/etc in a relational database manner should be pulled out (or at least copied out) into their own column(s).