1

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?

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

| improve this answer | |
  • 1
    Can that use an INDEX? Or must it read every row? – Rick James May 1 at 17:52
  • @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 May 1 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 May 1 at 20:10
0

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

| improve this answer | |
  • 1
    This is entirely possible, see my posted solution. – aowie1 Feb 27 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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