Efficiently Querying JSON Columns with Eloquent

// app/Models/User.php (example of JSON column casting)
namespace App\Models;

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

class User extends Model
{
    use HasFactory;

    protected $casts = [
        'settings' => 'array', // Or 'object' depending on your preference
    ];

    protected $fillable = ['name', 'email', 'settings'];
}

// Usage example in a controller or route
// Assuming 'settings' is a JSON column with data like:
// {"theme": "dark", "notifications": {"email": true, "sms": false}}

// Find users with a specific theme setting
$darkThemeUsers = App\Models\User::where('settings->theme', 'dark')->get();
/*
SELECT * FROM users WHERE settings->>'theme' = 'dark'; (PostgreSQL)
SELECT * FROM users WHERE JSON_EXTRACT(settings, '$.theme') = 'dark'; (MySQL)
*/

// Find users who have email notifications enabled
$emailNotifUsers = App\Models\User::where('settings->notifications->email', true)->get();
/*
SELECT * FROM users WHERE settings->'notifications'->>'email' = 'true'; (PostgreSQL)
SELECT * FROM users WHERE JSON_EXTRACT(settings, '$.notifications.email') = true; (MySQL)
*/

// Find users where a specific key exists in the JSON column
$usersWithSMSSetting = App\Models\User::whereJsonContains('settings->notifications', ['sms' => false])->get();
/*
SELECT * FROM users WHERE JSON_CONTAINS(settings, '{"notifications":{"sms":false}}'); (MySQL 5.7+)
SELECT * FROM users WHERE settings->'notifications' @> '{"sms":false}'; (PostgreSQL)
*/

// Update a specific value in a JSON column
App\Models\User::where('id', 1)->update(['settings->theme' => 'light']);

// Add a new key-value pair to JSON
App\Models\User::where('id', 2)->update(['settings->notifications->push' => true]);