CODEWITHSUNDEEP
phpmysqlsqljson
Xeen
Xeen

Reputation: 7003

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: [["88"],["90"],["91"]]

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 88 ?

Upvotes: 0

Views: 131

Answers (1)

BenM
BenM

Reputation: 53198

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.

Upvotes: 2

Related Questions