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?

2 Answers 2


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`

or.. in a WHERE:

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


  • 1
    Can that use an INDEX? Or must it read every row?
    – Rick James
    Commented May 1, 2020 at 17:52
  • 1
    @RickJames You can't INDEX the JSON column itself, but you can create virtual columns to extract scalar values from that JSON and INDEX on those
    – aowie1
    Commented May 1, 2020 at 18:15
  • There are some advantages with such an approach, but it is roughly equivalent to making it an explicit column with an explicit index.
    – Rick James
    Commented May 1, 2020 at 20:10

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

  • 1
    This is entirely possible, see my posted solution.
    – aowie1
    Commented Feb 27, 2020 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.