MySql like query in JSON field

Question

I have JSON type column named medium in my table

one of the field value like ["art", "nature"]

I want to get the rows with equivalent like query WHEREmedium LIKE %art%`

Refered this links but can't find a proper solution for this

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

Is this array or string...?

Gyandeep Sharma
– Gyandeep Sharma

2017-10-12 05:44:21 +00:00
Commented Oct 12, 2017 at 5:44 — Gyandeep Sharma
– Gyandeep Sharma, Commented Oct 12, 2017 at 5:44
field is an array

Jabaa
– Jabaa

2017-10-12 05:49:51 +00:00
Commented Oct 12, 2017 at 5:49 — Jabaa
– Jabaa, Commented Oct 12, 2017 at 5:49
How the data stored in the medium table ?

Sagar Jajoriya
– Sagar Jajoriya

2017-10-12 05:59:39 +00:00
Commented Oct 12, 2017 at 5:59 — Sagar Jajoriya
– Sagar Jajoriya, Commented Oct 12, 2017 at 5:59

Ihor Burlachenko · Accepted Answer · 2019-04-12 09:41:19Z

8

You still can use LIKE with JSON fields. It is a valid solution if you want to include keywords in some generic search functionality.

What I've noticed so far but do not have an explanation for is that LIKE is case-sensitive when applied to JSON fields (at least it is happening in MySQL 5.7.24). To overcome this problem, you can convert JSON fields to strings and then use LIKE.

SELECT *
FROM my_table
WHERE CONVERT(medium using 'utf8') LIKE '%art%';

edited Apr 12, 2019 at 9:41

answered Apr 12, 2019 at 9:34

Ihor Burlachenko

4,9331 gold badge30 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grantwparks Over a year ago

Awesome, thanks. Just slap a lowercase() on the operands and it's finding the rows I want.

Oddman · Accepted Answer · 2018-12-07 04:45:29Z

-2

I would advise not using a json type in this fashion. For one, JSON column types aren't that great for querying, they're a pita, and means you may be locked to one SQL implementation over another. Instead, you could either setup a separate table that contains a 1:n relationship with each record maintaining each of the mediums OR have a single column that has a comma-delimited string value with all the mediums. That then makes LIKE queries much easier and simpler - and indexable.

answered Dec 7, 2018 at 4:45

Oddman

4,0001 gold badge18 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySql like query in JSON field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related