Laravel Eloquent: Using whereJSONContains() to filter JSON columns

Introduction

In modern web applications, data is rarely just simple rows and columns. The rise of NoSQL databases like MongoDB and features in SQL databases like PostgreSQL and MySQL’s JSON data type have made it common to store complex, schema-less data in a JSON column. Laravel, the popular PHP framework, provides a powerful ORM called Eloquent which includes handy methods to interact with JSON columns. In this tutorial, we’ll learn how to use the whereJSONContains() method to filter query results based on JSON column values.

Prerequisites

• Understanding of PHP and Laravel framework
• Basic knowledge of Laravel Eloquent ORM
• Laravel project with a database that supports JSON column types (MySQL 5.7+, PostgreSQL 9.4+)

Basic Usage of whereJSONContains()

Let’s start with a simple example where we have a users table with a JSON column named options. This column might store user preferences or settings.

$users = User::whereJSONContains('options', ['dark_mode' => true])->get();

This query retrieves all the users who have dark mode enabled in their settings. Note: Remember to replace User with the actual model name relevant to your table.

Advanced Queries

JSON columns can store arrays, so you can query for elements within an array

$users = User::whereJSONContains('options->themes', 'dark')->get();

Here, it assumes that options column has an array under the key themes, and we’re searching for users who prefer the ‘dark’ theme.

Multiple Criteria

You may want to check multiple conditions in your JSON column for a more refined search. You can chain whereJSONContains() like any other query builder method.

$users = User::whereJSONContains('options', ['dark_mode' => true])
             ->whereJSONContains('options', ['language' => 'en'])
             ->get();

This will only return users who have dark mode enabled and have set their language preference to English.

Using with Other Eloquent Methods

One of the benefits of Eloquent is that you can combine different query methods to build your desired query. For instance, let’s combine whereJSONContains() with orWhere().

$users = User::whereJSONContains('options', ['notifications' => 'email'])
             ->orWhereJSONContains('options', ['notifications' => 'sms'])
             ->get();

This query will fetch users who have set their notification preference to either email or SMS.

Complex JSON Structures

The JSON column maybe even more complex, containing nested arrays and objects. You can still use whereJSONContains() to query this data efficiently.

$users = User::whereJSONContains('options', ['preferences' => ['theme' => 'dark', 'layout' => 'wide']])->get();

This query will return all users whose preferences include both a theme of ‘dark’ and a layout of ‘wide’.

Conclusion

We’ve seen that whereJSONContains() is a powerful tool for querying JSON columns in Laravel Eloquent. Whether your JSON structures are simple key-value pairs or complex arrays of objects, understanding how to construct these queries enables you to take full advantage of modern database capabilities directly from Laravel.