CODEWITHSUNDEEP
arraysjsonlaravellaravel-5
Antonio
Antonio

Reputation: 39

laravel update json query

I have mysql db with column that called "info", this is json column. I have there this json: 

{ 
   "pizza":{ 
      "sugar":"yes",
      "calorie":"100",
      "protein":"no"
   },
   "hamburger":{ 
      "sugar":"no",
      "calorie":"120",
      "protein":"yes"
   }
}

when I want to update for example the calorie of the pizza there is no problem:

DB::table('food')->where('id', '=', '1')
    ->update(array('info->pizza->calorie' => '90'));

then in the db i have:

{ 
   "pizza":{ 
      "sugar":"yes",
      "calorie":"90",
      "protein":"no"
   },
   "hamburger":{ 
      "sugar":"no",
      "calorie":"120",
      "protein":"yes"
   }
}

but when i want to add some food, for example chocolate:

DB::table('food')->where('id', '=', '1')
    ->update(array('info->chocolate->calorie' => '10'));

nothing happened.

In which way I can do that? thanks!

Upvotes: 1

Views: 4875

Answers (3)

nullthoughts
nullthoughts

Reputation: 11

Which version of Laravel are you using? I'm able to set new keys in JSON cast columns using both 5.7 and 5.8:

User Model:

protected $casts = [
   'phone_numbers' => 'json',
];

Updating:

User::first()->phone_numbers;

[ 'mobile' => '0000000000', ]

User::first()->update(['phone_numbers->office'] => '9999999999']);
User::first()->phone_numbers;

[ 'mobile' => '0000000000', 'office' => '9999999999', ]

Updating nested values:

User::first()->update(['phone_numbers->office->ext'] => '100']);
User::first()->phone_numbers;

[ 'mobile' => '0000000000', 'office' => [ 'ext' => '100', ], ]

Edit: You don't happen to have $fillable set for the info column, do you? If so, I believe you'll have to specify each individual property for mass-assignment:

protected $fillable = [
    'info->chocolate',
];

You can test it quickly by removing it from $fillable and setting $guarded to an empty array (temporarily):

protected $guarded = [];

Upvotes: 1

Udochukwu Enwerem
Udochukwu Enwerem

Reputation: 2823

You can't update non-existing keys for json columns in MySQL table. Look at this post to better understand the reason why.

To solve this problem, your best bet is to retrieve the column json data, decode it, insert new object entry, encode it again and finally update the table with the new json data.

$food = DB::table('food')->where('id', '=', '1')->first();
$info = json_decode($food->info);
$info->chocolate = (object)["calorie"=>"10"];

DB::table('food')->where('id', '=', '1')
    ->update(['info' => json_encode($info)]);

Upvotes: 1

kunwar97
kunwar97

Reputation: 825

I know this is the exact answer, as 2 queries have to be performed on DB. But this is a workaround. 

$food = DB::table('food')->where('id', '=', '1')->first();
$info = json_decode($food->info, true);
$info['chocolate']['calorie] = 10;
$food->info = json_encode(json_encode($info), JSON_FORCE_OBJECT);
$food->save();

Upvotes: 0

Related Questions