PHP
Efficiently Query JSON Columns in Laravel Eloquent Models
Learn how to interact with and query data stored in JSON columns directly through your Laravel Eloquent models, enabling flexible schema designs and powerful data retrieval.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
/**
* The attributes that should be cast.
*
* @var array
*/
protected $casts = [
'options' => 'array',
'metadata' => 'object',
];
/**
* The attributes that are mass assignable.
*
* @var array<int, string>
*/
protected $fillable = ['name', 'options', 'metadata'];
}
// Assuming a 'products' table with a JSON column 'options' and 'metadata'
// Example JSON in 'options': {"color": "red", "size": "L", "material": "cotton"}
// Example JSON in 'metadata': {"status": "available", "weight": 1.5}
// Create a product with JSON data
$product = Product::create([
'name' => 'T-Shirt',
'options' => ['color' => 'blue', 'size' => 'M'],
'metadata' => ['status' => 'in_stock', 'warehouse' => 'main'],
]);
// Query products where a specific JSON key has a certain value
$blueTShirts = Product::where('options->color', 'blue')->get();
// Query products where a nested JSON key has a certain value
$mainWarehouseProducts = Product::where('metadata->warehouse', 'main')->get();
// Update a specific key within a JSON column
$product->update(['options->size' => 'L']);
// Add a new key to a JSON column
$product->update(['options->fit' => 'regular']);
// Accessing JSON data
echo $product->options['color']; // Output: L (after update)
echo $product->metadata->status; // Output: in_stock
How it works: Laravel Eloquent provides robust support for interacting with JSON columns in your database. By defining a column as castable to `array` or `object` in your model's `$casts` property, Laravel automatically serializes and deserializes the JSON data. You can then query specific keys within these JSON columns using the `->` operator (e.g., `options->color`). This allows for flexible schema design where certain attributes don't require their own dedicated columns, making it easy to store and retrieve complex, unstructured data directly through your Eloquent models.