How to update a JSON data that came from a mysql database using laravel or just a native php?

Question

I have a database table with a type of longtext since I changed the structure to JSON because I will put a JSON data there and inserting there is is just fine. My problem is I want to update a part of that JSON data after retrieving it using laravel/php.

I tried it by using the code below but I have a hard time accomplishing it.

$qtyy = $orderDetail->quantity;  
$product = Product::findOrFail($prod_id);  
$json = json_decode($product->variations, true);  
foreach ($json as $key => $value) {  
if($key == $orderDetail->variation){  
    $total = (int)$value->qty + (int)$qtyy;  
    DB::table('products')  
        ->where('id', $prod_id)  
        ->update([$value->qty => $total]);  
    }  
}  

I want to change the "qty" in my database

{ 
   "AliceBlue-Cream":{ 
      "price":"500",
      "sku":"V-AliceBlue-Cream",
      "qty":"1000"
   },
   "Amethyst-Cream":{ 
      "price":"500",
      "sku":"V-Amethyst-Cream",
      "qty":"2998"             <- I want to update that
    }
 }

Answer 1

try this one, that's work for me!

DB::table('products')  
    ->where('id', $prod_id)
    ->update([
         "Amethyst-Cream->qty" => "2998"
      ]);