CODEWITHSUNDEEP
mysqllaraveleloquentmysql-json
Gilberto Albino
Gilberto Albino

Reputation: 2745

How to query array inside JSON column in Eloquent

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!

Upvotes: 3

Views: 5276

Answers (2)

tameemahmad shahzad
tameemahmad shahzad

Reputation: 577

Access the data like this

$product=$yourdata['products'][0];

Upvotes: 0

patricus
patricus

Reputation: 62228

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']])
    ]
);

Upvotes: 6

Related Questions