check if a value exists in json encode array in mysql

Question

I am working on a Mysql trigger to do something on my database. Before any thing, I want to check if a value exists in a json_encode string in my config table and after that proceed to run the trigger. my config table is handled by php scripts and looks like this:

-------------------------------------------
| config_name | config_value              |
-------------------------------------------
| target_id   | ["1","16","18","22","37"] |
-------------------------------------------

in php we can use: if in_array(json_decode(config_value)) and ...

but my problem is in Mysql syntax which doesn't support in_array and json_decode

How can I check if my value exists in 'config value' in Mysql trigger?

Answer 1

How to solve this problem

If you are storing JSON in mysql, make sure that you upgrade to mysql 5.7, then you can make use of the range of JSON functions available. In your particular case, you can do

   SELECT * FROM my_table WHERE JSON_SEARCH(config_value,"one", "17") IS NOT NULL;

What you Definitely ought to be doing

You have a problem in your data. If you find that you are always searching a JSON field, what that really means is that your table should be normalized.

update: section 2, title changed as suggested by @Sammitch