How to query array inside JSON column

I have successfully done some queries using JSON, 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!

gilbertoalbino

5 years ago

Level 1

I found that the solution as of Laravel 5.4+ is to use nested array as well in PHP:

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

Or in older versions of Laravel:

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

Please or to participate in this conversation.