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 www,sqlfiddle.com.. Also share your MySQL version with SELECT VERSION() — Raymond Nijland
– Raymond Nijland, Commented May 19, 2018 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
– Raymond Nijland, Commented May 19, 2018 at 10:34

Jonas Staudenmeir · Accepted Answer · 2018-06-05 00:49:13Z

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 5, 2018 at 0:49

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheSalamov · Accepted Answer · 2018-05-19 11:16:09Z

0

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 .

answered May 19, 2018 at 11:16

TheSalamov

9279 silver badges17 bronze badges