PHP
Querying JSON Columns with Laravel Eloquent
Learn how to effectively query and manipulate JSON column data in your Laravel Eloquent models, utilizing powerful methods like `whereJsonContains` and `->` operator.
// Assume a 'products' table with a 'options' JSON column, e.g.,
// {"color": "red", "size": "M", "tags": ["sale", "new"]}
// 1. Querying a specific key's value (JSON object)
$redProducts = App\Models\Product::where('options->color', 'red')->get();
// 2. Querying a value within a JSON array (whereJsonContains)
$saleProducts = App\Models\Product::whereJsonContains('options->tags', 'sale')->get();
// 3. Querying a deeply nested JSON path
// Assuming 'options' -> 'details' -> 'weight'
$heavyProducts = App\Models\Product::where('options->details->weight', '>', 500)->get();
// 4. Updating a JSON column value
$product = App\Models\Product::find(1);
$product->update([
'options->color' => 'blue',
'options->size' => 'L',
]);
// 5. Appending to a JSON array (PostgreSQL specific, or requires raw DB expression)
// For MySQL 5.7.8+ you can use JSON_ARRAY_APPEND (via DB::raw)
// $product = App\Models\Product::find(1);
// $product->options = array_merge((array)$product->options, ['tags' => array_merge($product->options['tags'], ['limited'])] );
// $product->save(); // Better to update the entire array or use casting for complex scenarios
How it works: Laravel Eloquent provides robust capabilities for interacting with JSON columns in your database. You can directly query specific keys or nested paths using the `->` operator, or check for the presence of values within JSON arrays using `whereJsonContains`. This functionality simplifies working with semi-structured data, allowing you to treat JSON fields almost like regular columns within your Eloquent queries. Updating JSON data is also straightforward by specifying the full path to the key.