CODEWITHSUNDEEP
mysqlselectnosqlwhere-clause
alexandernst
alexandernst

Reputation: 15089

MySQL WHERE a la NoSQL style

Is there a way I could search inside a JSON field in MySQL a la NoSQL? I mean, if I store, let's say, "{name: 'John', surname: 'Doe', age:'30'}" in a VARCHAR field, could I select and filter it with something like

SELECT my_json_field FROM my_table where my_json_field.name = 'John'

Regards

Upvotes: 0

Views: 141

Answers (3)

alexandernst
alexandernst

Reputation: 15089

There is no such thing. MySQL just won't understand JSON and writing regex matches or anything else is just asking for trouble.

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

Implementing such a feature with the like operator may kill performance. I advise to use MySQL fulltext search for this purpose instead of the like.

create table my_table (my_json_field text);

ALTER TABLE my_table ADD FULLTEXT(my_json_field);

insert into my_table (my_json_field) values ("{name: 'John', surname: 'Doe', age:'30'}");

insert into my_table (my_json_field) values ("{name: 'Pope', surname: 'Benedict', age:'666'}");

select *
from my_table
where MATCH(my_json_field) AGAINST ("Pope" IN BOOLEAN MODE);
'{name: ''Pope'', surname: ''Benedict'', age:''666''}'

select * 
from my_table
where MATCH(my_json_field) AGAINST ("John" IN BOOLEAN MODE);
'{name: ''John'', surname: ''Doe'', age:''30''}'

You may notice that I did not add the 'name' filedname to the query, because it is a stopword (that is ignored in the search term).

Also, you'll have to find a way to handle special characters if you want to search for a "fieldname: 'value'" pair.

Upvotes: 0

Razvan
Razvan

Reputation: 10093

Why not like this: 

 SELECT my_json_field FROM my_table where my_json_field LIKE '% name:\'John\' %'

Or more general, depending on the structure of your my_json_field:

 SELECT my_json_field FROM my_table where my_json_field LIKE '% name: \'John\' %'

Upvotes: 1

Related Questions