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

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).