Xeen Xeen - 1 year ago 61
JSON Question

How to write a MySQL query with WHERE in json

So I have a value saved in MySQL DB that's in the following format:


How could I write the query to be something like

SELECT * FROM table WHERE jsonColumn IN '88'
and it would find all the entries where in JSON data there is

Answer Source

You would be better looking for ["88"] using WHERE:

SELECT * FROM `table` WHERE `jsonColumn` LIKE '%["88"]%'

This will ensure that when searching for 88, values such as ["188"] are not incorrectly returned. Also, are you sure you need EVERYTHING from the table? Generally, SELECT * is not needed.

That having been said, it may be better to optimise your normalization so you're not storing JSON or serialized data. This will make your queries much more efficient and generally easier to work with, especially for more complex queries.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download