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!

this isn't what you want to hear, but the best option is to get the data out of the json column. If you need to use the data in queries, it should have it's own column in the table. In this case, you should have a products table. It looks like you have a code and a media. What does media reference? That looks like it should be a foreign key to a media_types table. — James Clark
– James Clark, Commented Nov 16, 2020 at 13:08
@Akina, I removed some keys, the JSON is ok. have fixed them in the post. — Gilberto Albino
– Gilberto Albino, Commented Nov 16, 2020 at 13:28
@JamesClarkDeveloper the medias come from another source, they are not in MySQL yet. However, I am using native MySQL JSON column type. — Gilberto Albino
– Gilberto Albino, Commented Nov 16, 2020 at 13:30
In MySQL you cannot search by both key and value at the same time. And you cannot search multi-instances. Provide desired output for shown JSON and searching criteria. — Akina
– Akina, Commented Nov 16, 2020 at 13:31

patricus · Accepted Answer · 2020-11-16 15:04:13Z

7

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

answered Nov 16, 2020 at 15:04

patricus

62.9k15 gold badges157 silver badges154 bronze badges

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

3 Comments

Gilberto Albino Over a year ago

Thank you! I didn't know one could nest arrays like so: ('attributes', ['products' => ['media' => '1']])

Sagar Naliyapara Over a year ago

Thanks @patricus I think this should be documented in Laravel doc

abr Over a year ago

JsonContains works perfectly for MySql, but do you. have any solution for sqlite? I've tried playing around whereRaw and json casts but to success.

tameemahmad shahzad · Accepted Answer · 2020-11-16 16:08:46Z

0

Access the data like this

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

answered Nov 16, 2020 at 16:08

tameemahmad shahzad

5773 silver badges8 bronze badges

1 Comment

Gilberto Albino Over a year ago

Yep! I am used to that :D

Collectives™ on Stack Overflow

How to query array inside JSON column in Eloquent

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related