Laravel eloquent update JSON column some fields without overriding everything else

Question

We are trying to use JSON datatypes to store Profile dynamic data, and do not know all of the hard-coded field names to add to the fillable property in model. how to set (insert if the key not exists | update if the key exists) just one key, not all of them?

For example if we want to update just age and we use:

$model = Profile::find($profile_id);
$model->options = json_encode(['age' => '21']);
$model->save();

this solution isn't a great option, because it overrides everything else. How can we set just the desired fields without changing other fields (JSON keys)?

Answer 1

the solution in pure mysql is:

UPDATE `profiles`
SET `data` = JSON_SET(
    `data` ,
    '$.age' ,
    '21'
)
WHERE
    `id` = 1;

The JSON_SET function will add the property if it is not found else replace it. read more here

But in Laravel you should set the Model $guarded attribute, and remove $fillable (or leave it blank), and then everything would be fillable except for guarded.

then you can use:

Profile::find($profile_id)->update([
   'data->age' => 21,
   'data->gender' => 'male',
]);

there are good experiences here

You can also use the following code as @Jonas Staudenmeir mentioned:

DB::table('...') with Profile::query()

https://laravel.com/docs/7.x/queries#updating-json-columns