CODEWITHSUNDEEP
mysqljsonlaravellaravel-5.4
JESlabbert
JESlabbert

Reputation: 160

Cannot get MYSQL to query JSON column correctly in Laravel

I am trying to query a JSON column in MYSQL using Laravel. I cannot get it to pick up records when I know there should be.

This is my select statement if I try it directly in MySQL after extracting from Laravel:

select `resources`.*, `users`.* from `resources` inner join `users` on `resources`.`user_id` = `users`.`id` where `status_id` = 3 and `resources`.`languages`->'$.""$"'.`name"` = English

I tried cleaning it up a bit according to the MySQL documentation:

select `resources`.*, `users`.* from `resources` inner join `users` on `resources`.`user_id` = `users`.`id` where `status_id` = 3 and `resources`.`languages`->"$.name" = "English"

My actual Laravel code is this:

        $query = DB::table('resources')
        ->join('users', 'resources.user_id', '=', 'users.id')
        ->select('resources.*', 'users.*')
        ->where('status_id', '=', 3);
    if (isset($languagesearch)) $query=$query->where('resources.languages->"$.name"', $languagesearch);
    if (isset($formatssearch)) $query=$query->whereRaw('JSON_EXTRACT(resources.formats, "$.name") = "'.$formatssearch.'"');

    $resources = $query->get();
    $resources = json_decode( json_encode($resources), true);

You I have two JSON columns and I have tried them both using two different methods but still no luck. It does set or unset the value correctly, it is just the where clause that is not working.

EDIT: The JSON Column format is: [{"name": "English"}, {"name": "German"}, {"name": "Portuguese"}]

My MySQL version is 5.7.18

Upvotes: 0

Views: 591

Answers (1)

patricus
patricus

Reputation: 62368

The Laravel query builder just uses plain -> operators. It will convert it to the correct syntax on the back end. Your where clause should just look like:

if (isset($languagesearch)) {
    $query = $query->where('resources.languages->name', $languagesearch);
}
if (isset($formatssearch)) {
    $query = $query->where('resources.formats->name', $formatssearch);
}

You can read more about json where clauses in the documentation here.

This, of course, assumes you're using a supported version of MySQL (at least 5.7.9).

Edit

Because you're trying to search for a certain value inside of a json array, you will need to use the JSON_CONTAINS() function. You may want to look at the documentation for json search functions here.

With the current way your json is structured, you would need something like:

if (isset($languagesearch)) {
    $query = $query->whereRaw('JSON_CONTAINS(resources.languages, \'{"name": "'.$languagesearch.'"}\')');
}

Upvotes: 1

Related Questions