querying json column in sql with Laravel

Question

Seofilter db table has json column named category_ids. I wanna query where value exists in json col.

And not querying all the row and get their jsoncol value then explode etc, I wanna do it with less query to server because of server loads.

for example [{60, 59, 57}] or [{55, 58, 60}] so 60 exists both of them.

Provide a proper table structure and example data on http://www,sqlfiddle.com.. Also share your MySQL version with `SELECT VERSION()` — Raymond Nijland, May 19 '18 at 10:32
"And not querying all the row and get their jsoncol value then explode etc, I wanna do it with less query to server because of server loads." Indexing JSON data is pretty limited in MySQL.. Because you can only index datatypes like a int, double, float or string with generated columns in MySQL.. You can't index a JSON array type an search it. — Raymond Nijland, May 19 '18 at 10:34

score 2 · Answer 1 · answered Jun 05 '18 at 00:49

2

Laravel 5.6.24 includes whereJsonContains():

DB::table('seofilter')->whereJsonContains('category_id', 60)->get();
DB::table('seofilter')->whereJsonContains('category_id', [60, 61, ...])->get();

answered Jun 05 '18 at 00:49

Jonas Staudenmeir

score 0 · Answer 2 · answered May 19 '18 at 11:16

MySQL has a group of JSON manipulating and searching function i believe you are going to find what you want over here start with JSON_CONTAINS https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains
which clearly fits the example you provided .

2 Answers2