PHP
Query and Manipulate JSON Columns with Laravel Eloquent
Efficiently query and manipulate JSON data stored in database columns using Laravel Eloquent's built-in methods like `whereJsonContains` and `whereJsonLength`.
// Example: A 'settings' JSON column on a User model
// In your User migration: $table->json('settings')->nullable();
// In your User model: protected $casts = ['settings' => 'array'];
use App\Models\User;
// Find users whose settings contain a specific key-value pair
$usersWithPreference = User::whereJsonContains('settings->preferences->theme', 'dark')
->get();
echo "Users with dark theme preference: " . $usersWithPreference->count() . "
";
// Find users where a specific array in JSON has a certain value
$usersWithNotifications = User::whereJsonContains('settings->notifications', 'email')
->get();
echo "Users with email notifications: " . $usersWithNotifications->count() . "
";
// Find users where a JSON array (e.g., 'tags') has a specific length
$usersWithTwoTags = User::whereJsonLength('settings->tags', 2)
->get();
echo "Users with exactly 2 tags: " . $usersWithTwoTags->count() . "
";
// Find users where a JSON array's length is greater than a value
$usersWithMoreThanOneTag = User::whereJsonLength('settings->tags', '>', 1)
->get();
echo "Users with more than 1 tag: " . $usersWithMoreThanOneTag->count() . "
";
How it works: This snippet demonstrates how to effectively query JSON columns in a database using Eloquent. It covers `whereJsonContains()`, which checks if a JSON array or object contains a specific value at a given path, and `whereJsonLength()`, used to filter records based on the length of a JSON array at a specified path. This functionality is powerful for handling semi-structured data directly within your database queries.