How to query array inside JSON column in Eloquent

Question

I have successfully done some queries using JSON in Eloquent, but I can't figure out how to query a value from an object inside an array like this:

In column "attributes", there is :

{
    "products": [
        {
            "media": "1",
            "code": "4186GSB"
        },
        {
            "media": "2",
            "code": "4186GSE"
        }
    ]
}

I would like to query "media" = 1

I have so far tried:

$query->where('attributes->products->media', 1); // failed
// or
$query->where('attributes->products[*]->media', 1); // failed

I have no clue how to use a raw query, but it's okay if that's the solution!

Answer 1

You need to use the whereJsonContains() query method to query a json array (available as of Laravel 5.6.24). Your query would look like:

$query->whereJsonContains('attributes', ['products' => ['media' => '1']]);

If you're on an earlier version and don't have this function available, you can use a raw query:

$query->whereRaw(
    'json_contains(attributes, ?)',
    [
        json_encode(['products' => ['media' => '1']])
    ]
);