how to check whether "knownlanguages" key contains value ="English" from json datatype of mysql using query

Question

{
  "Actor": {
    "knownlanguages": [
      "English"
    ]
  }
}

This JSON is stored in JSON columntype of MySQL with name data.

My question is how to check whether knownlanguages key contains value English from JSON datatype of MySQL using query?

Answer 1

you can use like keyword with wildcard %

SELECT * FROM table where data like "%English%"

NOTE:

this won't search in JSON field knownlanguages, but in column data

Answer 2

Easy:

SELECT * FROM table WHERE JSON_CONTAINS(json, '"English"', "$.Actor.knownlanguages")

or (depending on what you have to do):

SELECT JSON_CONTAINS(json, '"English"', "$.Actor.knownlanguages") FROM table

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