How to set a value in a null JSON column with Laravel's Eloquent?

Question

I'm using Laravel 7.

I want to update a JSON column using Laravel's Eloquent. The problem is that if the value of the column is null the column won't be updated.

This is how the code looks like:

Model::update(['jsonColumnName->jsonColumnKey' => 'value']);

This is the SQL that it would generate:

UPDATE model
SET jsonColumnName = JSON_SET(jsonColumnName, '$.jsonColumnKey', 'value');

According to the documentation of JSON_SET, it will take the first parameter as the JSON document that it will modify. In this case, that value would be null since jsonColumnName is currently null. Because of that it returns null since it has nothing to modify and it ends doing nothing.

If I manually set the value of the column to {} and run the showed code, it works. But I guess that you are not expected to do that (right?).

Answer 1

You should make new alter table migration and change json column to have default value {}.

First you need to check if there is already installed dbal with

composer require doctrine/dbal

Then make new migration with code in up() method:

Schema::table('table_name', function (Blueprint $table) {
    $table->json('column_name')->nullable()->default(null)->change();
});

Don't forget to backup database before work on it.

With NULL value you can also check if that field is empty.

Another way, on framework level is to set logic about this issue into model's observer.

For example:

public function saving(EntityModel $entityModel)
{
    if (is_null($entityModel->json_column)) {
        $entityModel->json_column = '{}';
    }
}