Append array to JSON column to multiple rows at once

Issue

I have a column type JSON in database, and I’m performing an update on multiple rows.

For example, this query

Model::whereIn('id',$ids)->update([
   'status' => 'canceled'
]);

And this table has another column called history (JSON type), each row already has its own history in JSON.

How do I append to each one of them? This array, for example

[
   'user_id' => '144',
   'action'  => 'cancel',
   'at'      => '2021 - 08 - 30'
]

My idea and question, is there something like

Model::whereIn( 'id', $ids )->appendJson('field_name',$array);

Solution

The easiest approach could be like this:

$status = 'canceled';
$extraHistoryData = [...];

Model::whereKey($ids)->get(function ($model) use ($status, $extraHistoryData) {
    $history = $model->history;

    $history = array_merge($history, $extraHistoryData);

    $model->update(compact('status', 'history'));
});

If you want to update all rows in a single query, the closest I can get is this thread. But It’s not very likely to solve your problem.

Answered By – Kevin Bui

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0