Search code examples
phpjsonpostgresqleloquentjsonb

Laravel Eloquent - having problems updating JSON col in PostgreSQL

Part 1: Main Question

On my colours_tab table (PostgreSQL 9.6), I have a column called colours_json_col of type JSONB. I have the following code in my Laravel controller which creates a new record on the afore-mentioned table:

$colour_rec = new ColoursModel();
$colour_rec -> colours_json_col = ['fruits' => ['apple' => 'pink'] ];
$colour_rec -> update (['colours_json_col->fruits->apple' => 'green']);
$colour_rec -> saveOrFail();

The above code doesn't error or throw an exception, yet after executing it the new record in my database contains the following JSON data:

{"fruits": {"apple": "pink"}}

Obviously, what I want is for the apple to be green, not pink! The documentation here (https://laravel.com/docs/5.5/queries#updates) suggests that I'm doing the right thing in order set it 'green'. Can anyone see what I'm doing wrong?

Part 2: Bonus Question

In order to prevent the above code from erroring, I have had to declare the column colours_json_col as $fillable in the model:

protected $fillable = ['colours_json_col'];

I don't really want to set the column as $fillable if at all possible. Therefore, is there a way to update the value without having to call the update() function?

Solution

  • Answering both parts of the question in one:

    $colour_rec = new ColoursModel();
$colour_rec -> colours_json_col = ['fruits' => ['apple' => 'pink'] ];
$colour_rec -> setAttribute ('colours_json_col->fruits->apple', 'green');
$colour_rec -> saveOrFail();

    It seems to be an undocumented feature in Eloquent, as far as I can tell.