PHP
Query JSON Columns in Laravel Eloquent
Efficiently query and filter Eloquent models based on data stored within JSON-type columns in your database tables using Laravel's powerful methods.
<?php
namespace App\Http\Controllers;
use App\Models\Order;
use Illuminate\Http\Request;
class OrderController extends Controller
{
public function index()
{
// Assume an 'options' JSON column in the orders table
// with data like: {'delivery_info': {'method': 'express', 'address': '123 Main St'}}
// or {'items': [{'id': 1, 'qty': 2}, {'id': 2, 'qty': 1}]}
// 1. Find orders where 'delivery_info.method' in JSON column 'options' is 'express'
$expressOrders = Order::where('options->delivery_info->method', 'express')->get();
// 2. Find orders where an item with id 5 exists in the 'items' JSON array
$ordersWithSpecificItem = Order::whereJsonContains('options->items', ['id' => 5])->get();
// 3. Find orders where the 'items' JSON array has exactly 3 elements
$ordersWithThreeItems = Order::whereJsonLength('options->items', 3)->get();
// 4. Find orders where the 'items' JSON array has more than 1 element
$ordersWithMultipleItems = Order::whereJsonLength('options->items', '>', 1)->get();
// 5. Select a specific value from a JSON column
$orderAddresses = Order::select('id', 'options->delivery_info->address as delivery_address')->get();
return view('orders.index', compact('expressOrders', 'ordersWithSpecificItem', 'ordersWithThreeItems', 'ordersWithMultipleItems', 'orderAddresses'));
}
}
How it works: Laravel Eloquent provides intuitive methods for querying JSON columns, which are increasingly common for flexible data storage. You can query nested JSON properties using the `->` operator (e.g., `options->delivery_info->method`). For checking the presence of values within a JSON array, `whereJsonContains` is ideal. `whereJsonLength` allows you to filter based on the number of elements in a JSON array. These methods abstract away complex SQL for JSON operations, making queries readable and maintainable.