PHP
Query JSON Data Stored in Database Columns with Eloquent
Explore how to efficiently query and manipulate JSON data stored directly within database columns using Laravel Eloquent's powerful query builders.
// Assuming a Product model with a 'details' JSON column
// The 'details' column might store data like:
// {"color": "red", "weight": "2kg", "dimensions": {"width": 10, "height": 5}}
use App\Models\Product;
// Find products where 'details->color' is 'red'
$redProducts = Product::where('details->color', 'red')->get();
// Find products where 'details->dimensions->width' is greater than 10
$wideProducts = Product::where('details->dimensions->width', '>', 10)->get();
// Update a specific key within the JSON column
Product::where('id', 1)->update(['details->color' => 'blue']);
// Check if a JSON array contains a specific value
// Assuming 'tags' is a JSON array: ["electronics", "sale"]
// Product::whereJsonContains('tags', 'sale')->get();
// Check if a JSON array has a specific length (PostgreSQL, MySQL 8+)
// Product::whereJsonLength('tags', 2)->get();
How it works: Laravel Eloquent simplifies querying JSON columns in your database. You can access nested JSON properties using the `->` operator directly within `where` clauses (e.g., `'details->color'`). This allows you to filter records based on specific values within the JSON structure. You can also update specific keys using the same `->` syntax in `update` methods. Additionally, methods like `whereJsonContains` and `whereJsonLength` (database dependent) offer more advanced JSON querying capabilities.