How to Query for Email Addresses within a JSON Column in Laravel?

Retrieving Data from a JSON Column in Laravel

Problem:

In a Laravel application, you have a to column of type JSON in the emails table, storing email addresses in the following format:

<code class="JSON">[
    {
        "emailAddress": {
            "name": "Test", 
            "address": "[email&#160;protected]"
        }
    }, 
    {
        "emailAddress": {
            "name": "Test 2", 
            "address": "[email&#160;protected]"
        }
    }
]</code>

You need to retrieve a collection of all emails sent to "[email protected]".

Solution:

Using Laravel's Eloquent, you can search for specific values within JSON columns using the whereJsonContains method. However, your initial query:

<code class="php">DB::table('emails')->whereJsonContains('to->emailAddress->address', '[email&#160;protected]')->get();</code>

does not return a match because the arrow operator (->) is not valid within array keys. To resolve this issue, use nested arrays instead:

<code class="php">DB::table('emails')
   ->whereJsonContains('to', [['emailAddress' => ['address' => '[email&#160;protected]']]])
   ->get();</code>

This query will correctly search for email addresses equal to "[email protected]" and return the desired collection.

The above is the detailed content of How to Query for Email Addresses within a JSON Column in Laravel?. For more information, please follow other related articles on the PHP Chinese website!