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)?

This is your best option in Laravel: laravel.com/docs/7.x/queries#updating-json-columns You can replace DB::table('...') with Profile::query(). — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 6, 2020 at 15:44

miken32 · Accepted Answer · 2024-02-29 16:40:14Z

0

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 ensure that the model's $fillable property has the column in it:

$fillable = [
    ...
    "data->age",
    ...
];

then you can use:

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

edited Feb 29, 2024 at 16:40

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Apr 6, 2020 at 12:35

SoheilYou

9495 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel eloquent update JSON column some fields without overriding everything else

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related