Pushing data instead of updating json colum in Laravel-CodePudding

I need some help related to updating a table column of type json.

Here is a scenario, I have an api call from an external api.And the response, there are instances that it contains multiple same ids with different values. So my goal is to add them all in a json column if it is belong to the same id.

So example

  foreach(responses as response) {
     //here the response->id occured twice and have different response->value
     // response->id(123)
     // response->id(123
     DB::table('table')->where('res_id', response->id)->update['myvalues' => json_decode(response->value);
  }

The problem with this approach is the first response->value that already saved in db for the multiple response->id(same), it will be overwritten in the next iteration if the response->id is same. So what I need is to append it instead of updating.

The myvalues json column has a format like this

       [
         {
            "Fee": "TshirtFee",
             "FeeAmount": {
             "CurrencyCode": "EUR",
             "CurrencyAmount": -2.87
         },
         ................
        ]

Any help?

CodePudding user response：

You cannot simply update, as that will, as you have found out, overwrite the existing value.

You need to retrieve the existing data (assuming there is some) and decode it to an array. Then you need to push the new data on to the array. Then re-encode it as JSON and save it back to the database. I am currently using a similar process to update usage statistics of a third party platform.

CodePudding user response：

I already sort it out.

foreach(responses as response) {
  //here the response->id occured twice and have different response->value
  // response->id(123)
  // response->id(123
   $key =  $response->id;
   $output[$key][]= $response->value;
  DB::table('table')->where('res_id', response->id)->update['myvalues' => json_decode(response->value);
}

Hope this will help others.