Search in Json column with Laravel

Question

In my emails table, I have a column named To with column-type Json. This is how values are stored:

[
    {
        "emailAddress": {
            "name": "Test", 
            "address": "[email protected]"
        }
    }, 
    {
        "emailAddress": {
            "name": "Test 2", 
            "address": "[email protected]"
        }
    }
]

Now I want a collection of all emails sent to "[email protected]". I tried:

DB::table('emails')->whereJsonContains('to->emailAddress->address', '[email protected]')->get();

(see https://laravel.com/docs/5.7/queries#json-where-clauses) but I do not get a match. Is there a better way to search using Laravel (Eloquent)?

In the debugbar, I can see that this query is "translated" as:

select * from `emails` where json_contains(`to`->'$."emailAddress"."address"', '\"[email protected]\"'))

Solution 1

The arrow operator doesn't work in arrays. Use this instead:

DB::table('emails')
   ->whereJsonContains('to', [['emailAddress' => ['address' => '[email protected]']]])
   ->get()

Solution 2

I haven't used the json column but as the documentation refers, the below code should work fine.

DB::table('emails')
  ->where('to->emailAddresss->address','[email protected]')
  ->get();

Solution 3

In case to store array in json format. And just have an array list of IDs, I did this.

items is the column name and $item_id is the term I search for

// $item_id = 2
// items = '["2","7","14","1"]'
$menus = Menu::whereJsonContains('items', $item_id)->get();

Comments

[user3253002]

I've tried with ->where and with ->whereJsonContains, but no luck ..

[Jenish Shrestha]

Can you include your table schema so we have better knowledge of the table.

[Evan Lalo]

Is there a way to do this with a wild card? For example, Activity::whereJsonContains('properties', 'like', ['name' => "%$query%"])->get();

[Jonas Staudenmeir]

@EvanLalo No, MySQL doesn't support that.

[Jaber Al Nahian]

He is talking about json column. I think you are wrong

[Florian Lauterbach]

There will be problems if there is a address like [email protected] for example because it will be found too. So it will work, but it's not the best choice.

[Tofandel]

Pretty sure it's ->where('to->"$.emailAddresss.address"','[email protected]')

[dipenparmar12]

Hi, its working fine, can you share reference document.

[JG_GJ]

@Tofandel where I find documentation of that, I have spent hours trying to search for a value that is in an array that is in the json.