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
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.
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%';