laravel如何处理JSON字段的查询和更新_Laravel JSON字段查询与更新方法

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->json('settings'); // 定义 JSON 字段
    $table->timestamps();
});

// 查询 settings 中 language 为 'zh' 的用户
$users = DB::table('users')
    ->where('settings->language', 'zh')
    ->get();

// 使用 Eloquent
$users = User::where('settings->language', 'zh')->get();

// 查询 settings 中 preferences->theme 为 'dark' 的用户
User::where('settings->preferences->theme', 'dark')->get();

// MySQL: 使用 JSON_CONTAINS_PATH
User::whereJsonContains('settings', '->notifications')->get();

// 或检查嵌套路径是否存在
User::whereJsonContains('settings', '->preferences->email')->get();

// 假设 settings->roles 是一个数组，查找包含 'admin' 的用户
User::whereJsonContains('settings->roles', 'admin')->get();

DB::table('users')->where('id', 1)->update([
    'settings->language' => 'en'
]);

User::where('id', 1)->update([
    'settings->theme' => 'light'
]);

User::where('id', 1)->update([
    'settings->language' => 'en',
    'settings->timezone' => 'UTC',
    'settings->preferences->email' => true,
]);

// 向 roles 数组中添加一个新角色
User::where('id', 1)->update([
    'settings->roles' => DB::raw("JSON_ARRAY_APPEND(settings, '$.roles', 'editor')")
]);

class User extends Model
{
    protected $casts = [
        'settings' => 'array', // 自动转为数组
    ];
}

$user = User::find(1);
$user->settings['language'] = 'zh';
$user->save();

$user->update([
    'settings' => [
        'language' => 'zh',
        'theme' => 'dark',
        'roles' => ['user', 'vip']
    ]
]);