PHP
Querying JSON Columns in Laravel Eloquent
Discover how to efficiently query and update JSON columns in your database using Laravel Eloquent's powerful built-in functionalities.
// In your migration file
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->json('details')->nullable(); // JSON column
$table->timestamps();
});
// In your App\Models\Product model
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
protected $fillable = ['name', 'details'];
}
// Usage examples
// Create a record with JSON data
App\Models\Product::create([
'name' => 'Laptop',
'details' => [
'processor' => 'Intel i7',
'ram' => '16GB',
'storage' => [
'type' => 'SSD',
'size' => '512GB'
],
'features' => ['backlit keyboard', 'fingerprint reader']
]
]);
// Querying a JSON column (e.g., where 'details->processor' is 'Intel i7')
$laptops = App\Models\Product::where('details->processor', 'Intel i7')->get();
// Querying nested JSON (e.g., where 'details->storage->type' is 'SSD')
$ssdProducts = App\Models\Product::where('details->storage->type', 'SSD')->get();
// Querying JSON arrays (e.g., where 'details->features' contains 'backlit keyboard')
$featuredProducts = App\Models\Product::whereJsonContains('details->features', 'backlit keyboard')->get();
// Updating a specific key within a JSON column
$product = App\Models\Product::find(1);
$product->update([
'details->ram' => '32GB'
]);
How it works: Laravel Eloquent provides excellent support for interacting with JSON columns in your database. You can define a `json` column in your migration. Eloquent allows you to query specific keys within JSON data using the `->` operator (e.g., `details->processor`). For arrays within JSON, `whereJsonContains()` is used to check if a value exists in the array. You can also update specific keys within a JSON column directly using the same `->` notation in the `update()` method.