Cannot get MYSQL to query JSON column correctly in Laravel

Question

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

patricus · Accepted Answer · 2017-10-26 17:57:59Z

1

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.'"}\')');
}

edited Oct 26, 2017 at 17:57

answered Oct 26, 2017 at 8:07

patricus

62.9k15 gold badges157 silver badges154 bronze badges

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

2 Comments

JESlabbert Over a year ago

Hi Patricus, I have tried that but still unfortunately no luck. The format inside the json column is like so: [{"name": "English"}, {"name": "German"}, {"name": "Portuguese"}]

JESlabbert Over a year ago

Awesome! That worked perfectly! Thanks so much @patricus

Collectives™ on Stack Overflow

Cannot get MYSQL to query JSON column correctly in Laravel

1 Answer 1

Edit

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related