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, Nov 16 '20 at 13:08
@Akina, I removed some keys, the JSON is ok. have fixed them in the post. — Gilberto Albino, Nov 16 '20 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, Nov 16 '20 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, Nov 16 '20 at 13:31

score 6 · Accepted Answer · answered Nov 16 '20 at 15:04

6

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 '20 at 15:04

patricus

59,488
15
143
145

1

Thank you! I didn't know one could nest arrays like so: ('attributes', ['products' => ['media' => '1']]) – Gilberto Albino Nov 16 '20 at 17:25
Thanks @patricus I think this should be documented in Laravel doc – Sagar Naliyapara Jun 23 '21 at 03:03
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. – abr Sep 14 '21 at 13:35

score 0 · Answer 2 · answered Nov 16 '20 at 16:08

0

Access the data like this

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

answered Nov 16 '20 at 16:08

tameemahmad shahzad

549
3
8

Yep! I am used to that :D – Gilberto Albino Nov 16 '20 at 17:26

How to query array inside JSON column in Eloquent

2 Answers2

Linked