laravel update json query

Question

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!

I see a pizza object, but I don't see a chocolate object anywhere in your code. — Udochukwu Enwerem
– Udochukwu Enwerem, Commented Sep 8, 2019 at 13:33
@UdoE. there is no chocolate in the code. i want to add the chocolate like the pizze and the hamburger... — Antonio
– Antonio, Commented Sep 8, 2019 at 14:09

Udochukwu Enwerem · Accepted Answer · 2019-09-08 16:34:58Z

1

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)]);

answered Sep 8, 2019 at 16:34

Udochukwu Enwerem

2,8932 gold badges28 silver badges37 bronze badges

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

Comments

nullthoughts · Accepted Answer · 2019-09-20 00:44:25Z

1

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 = [];

edited Sep 20, 2019 at 0:44

answered Sep 19, 2019 at 21:33

nullthoughts

113 bronze badges

1 Comment

Dmitry Chernikov Over a year ago

There are slightly more opening square brackets than closing brackets. And by the way it doesn't work in laravel 8

kunwar97 · Accepted Answer · 2019-09-08 13:14:56Z

0

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();

answered Sep 8, 2019 at 13:14

kunwar97

8257 silver badges15 bronze badges

Collectives™ on Stack Overflow

laravel update json query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related