Efficiently Query and Manipulate JSON Columns in Eloquent

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    use HasFactory;

    /**
     * The attributes that should be cast.
     *
     * @var array
     */
    protected $casts = [
        'details' => 'array', // Casts the 'details' JSON column to a PHP array
    ];

    protected $fillable = ['customer_id', 'total_amount', 'details'];
}

// Assuming an 'orders' table with a JSON column 'details' like:
// {
//   "items": [
//     {"product_id": 1, "name": "Laptop", "price": 1200},
//     {"product_id": 3, "name": "Mouse", "price": 25}
//   ],
//   "shipping": {
//     "address": "123 Main St",
//     "city": "Anytown"
//   },
//   "payment_method": "Credit Card"
// }

// Usage:

// 1. Querying a specific key in a JSON column
// $ordersWithCreditCard = Order::where('details->payment_method', 'Credit Card')->get();

// 2. Querying nested JSON data
// $ordersFromAnytown = Order::where('details->shipping->city', 'Anytown')->get();

// 3. Querying JSON arrays (whereJsonContains)
// Find orders that contain product_id 1 in their 'items' array
// (Note: This checks if the array contains an object with 'product_id: 1' as a property, not just a scalar value 1)
// $ordersContainingProduct1 = Order::whereJsonContains('details->items', ['product_id' => 1])->get();

// 4. Updating a JSON property
// $order = Order::find(1);
// $order->update(['details->shipping->city' => 'Newtown']);

// 5. Appending to a JSON array (MySQL 5.7+ / PostgreSQL)
// $order = Order::find(1);
// $newItem = ['product_id' => 5, 'name' => 'Keyboard', 'price' => 75];
// $order->update(['details->items' => json_encode(array_merge($order->details['items'], [$newItem]))]);
// Note: For appending, directly manipulating the casted attribute is often cleaner:
// $order->details = array_merge($order->details, ['new_key' => 'new_value']); // For top-level
// $order->details['items'][] = $newItem; // For nested array
// $order->save();