Querying and Updating JSON Columns in Laravel Eloquent Models

use Illuminate\Database\Eloquent\Model;

// In a model (e.g., app/Models/Product.php) where 'options' is a JSON column
class Product extends Model
{
    protected $fillable = ['name', 'options'];
    protected $casts = [
        'options' => 'array', // or 'collection'
    ];
}

// Create some example products
Product::create(['name' => 'Red T-Shirt', 'options' => ['color' => 'red', 'size' => 'M', 'tags' => ['sale', 'new']]]);
Product::create(['name' => 'Blue Jeans', 'options' => ['color' => 'blue', 'size' => 'L', 'tags' => ['clearance']]]);
Product::create(['name' => 'Green Hat', 'options' => ['color' => 'green', 'size' => 'One Size', 'tags' => ['new']]]);

// Find products where 'color' in the 'options' JSON column is 'red'
$redProducts = Product::where('options->color', 'red')->get();
echo "Red Products: " . $redProducts->pluck('name')->implode(', ') . "
";

// Find products where 'tags' (an array within 'options') contains 'sale'
$saleProducts = Product::whereJsonContains('options->tags', 'sale')->get();
echo "Sale Products: " . $saleProducts->pluck('name')->implode(', ') . "
";

// Update a specific key within a JSON column using model instance
$productToUpdate = Product::where('name', 'Blue Jeans')->first();
if ($productToUpdate) {
    // First, retrieve the existing options and merge new/updated values
    $options = $productToUpdate->options;
    $options['material'] = 'denim';
    $options['size'] = 'XL'; // Update existing key
    $productToUpdate->options = $options;
    $productToUpdate->save();
    echo "Updated 'Blue Jeans' options: " . json_encode($productToUpdate->options) . "
";
}

// Or update a specific key within a JSON column directly using a query
Product::where('name', 'Green Hat')->update(['options->price' => 29.99]);
$updatedGreenHat = Product::where('name', 'Green Hat')->first();
echo "Updated 'Green Hat' options: " . json_encode($updatedGreenHat->options) . "
";