Querying and Manipulating JSON Columns in Eloquent

// In your model:
// app/Models/Settings.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Settings extends Model
{
    protected $casts = [
        'preferences' => 'array', // Casts the JSON column to a PHP array
        'options' => 'object',    // Casts the JSON column to a PHP object
    ];

    protected $fillable = ['user_id', 'preferences', 'options'];
}

// In your migration (for MySQL 5.7+ / PostgreSQL 9.4+):
// use Illuminate\Database\Schema\Blueprint;
// use Illuminate\Support\Facades\Schema;
//
// Schema::create('settings', function (Blueprint $table) {
//     $table->id();
//     $table->foreignId('user_id')->constrained();
//     $table->json('preferences')->nullable(); // JSON column
//     $table->json('options')->nullable();     // Another JSON column
//     $table->timestamps();
// });

// Example Usage:

// 1. Storing data
$settings = Settings::create([
    'user_id' => 1,
    'preferences' => [
        'theme' => 'dark',
        'notifications' => [
            'email' => true,
            'sms' => false,
        ],
    ],
    'options' => (object) ['currency' => 'USD', 'timezone' => 'UTC'],
]);

// 2. Retrieving data (automatically cast)
$userSettings = Settings::find(1);
echo $userSettings->preferences['theme']; // Access as array
echo $userSettings->options->currency;   // Access as object

// 3. Querying JSON columns
// Where a specific key exists
$darkThemeUsers = Settings::whereJsonContains('preferences->theme', 'dark')->get();

// Where a nested key matches a value
$emailNotificationsUsers = Settings::whereJsonContains('preferences->notifications->email', true)->get();

// Where a value is greater than a certain threshold (e.g., if you store numbers)
// Settings::where('data->price', '>', 100)->get();

// 4. Updating JSON columns
$settings->preferences = array_merge($settings->preferences, ['language' => 'en']);
$settings->save();

// Or using update method for specific JSON path (Laravel 8+)
Settings::where('user_id', 1)->update([
    'preferences->notifications->sms' => true,
    'options->timezone' => 'America/New_York',
]);