CODEWITHSUNDEEP
phpmysqljsonlaravellaravel-5
user3253002
user3253002

Reputation: 1671

Search in Json column with Laravel

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]\"'))

Upvotes: 25

Views: 62950

Answers (7)

Divesh Chakrayat
Divesh Chakrayat

Reputation: 21

This is the correct way to Where conditions on JSON columns

Model::whereJsonContains('coulmn_name->key_from_json', value_from_json)

Upvotes: 1

Brotherbond
Brotherbond

Reputation: 31

Using Eloquent => Email::where('to->emailAddress->address','[email protected]')->get();

Upvotes: 1

humaneguy
humaneguy

Reputation: 11

Checkout the Laravel API docs for the whereJsonContains method https://laravel.com/api/8.x/Illuminate/Database/Query/Builder.html#method_whereJsonContains

Upvotes: 1

Nasser Ali Karimi
Nasser Ali Karimi

Reputation: 4663

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();

Upvotes: 8

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

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

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

Upvotes: 52

arun
arun

Reputation: 92

You can use where clause with like condition

DB::table('emails')->where('To','like','%[email protected]%')->get();

Alternatively, if you have Model mapped to emails table names as Email using Eloquent

Email::where('To','like','%[email protected]%')->get();

Upvotes: -2

Jenish Shrestha
Jenish Shrestha

Reputation: 224

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();

Upvotes: 16

Related Questions