Querying JSON Columns in Laravel 11 Using `whereJsonContains`

In Laravel, the whereJsonContains method allows you to query JSON columns in your database. This method is useful when you need to filter records based on the presence of a specific value within a JSON column. whereJsonContains method provides a powerful way to query JSON data stored in your database.

Understanding whereJsonContains

The whereJsonContains method provides a powerful way to query JSON data stored in your database. This method can handle both simple and complex JSON structures.

Basic Usage

Let's start with a simple example. Suppose you have a users table with a preferences column that stores JSON data. You want to retrieve users who have specified email notifications.

$users = DB::table('users')
        ->whereJsonContains('preferences->notifications', 'email')
        ->get();

Querying Multiple Values

You can also pass an array to whereJsonContains to check for multiple values. For example, if you want to find users who have specified either email or sms notifications, you can do the following:

$users = DB::table('users')
        ->whereJsonContains('preferences->notifications', ['email', 'sms'])
        ->get();

Querying Nested JSON Objects

If your JSON column contains nested objects, you can use the arrow notation to access the nested keys.

{
  "preferences": {
    "notifications": {
      "methods": ["email", "sms"],
      "frequency": "daily"
    },
    "theme": "dark"
  }
}

To query users based on the presence of email in the nested methods array, you can use the following query:

$users = User::whereJsonContains('preferences->notifications->methods', 'email')->get();

Real-World Example

{
    "items": [
        {
            "name": "Laptop",
            "category": "electronics",
            "quantity": 1
        },
        {
            "name": "Phone",
            "category": "electronics",
            "quantity": 2
        }
    ]
}

To find orders that contain at least one electronics item, you can use the following query:

$orders = DB::table('orders')->whereJsonContains('details->items->category', 'eletronics')->get();