CODEWITHSUNDEEP
phplaravel
ilham zacky
ilham zacky

Reputation: 51

How To Get Sum On Multiple Jsonb Values Laravel

I am running a cron each Weak and saving customers' data on a new table, For a customer per month 4 records, End of every month I am running a new cron and need to get the sum on customer data and save it on a new table to send some reports.

weak data is saved like this

[customer_id = 25
data {
  "body": {
    "aaa": 0,
    "bbb": 98,
    "ccc": 0,
    "ddd": 1,
  }
}
],
[customer_id = 25
data {
  "body": {
    "aaa": 22,
    "bbb": 22,
    "ccc": 22,
    "ddd": 12,
  }
}
]

since it is a JSON b column I couldn't get the sum

This is my code

 $summaryReport = summaryreport::all();
                   
 foreach($summaryReport as $report){
          
     $data['aaa'][$report->customer_id] = (json_decode($report->data)->body->aaa);
    }
     Model::insert($data);
    ``

Upvotes: 0

Views: 296

Answers (1)

mrhn
mrhn

Reputation: 18976

Firstly filter your reports by using the collection method where(), to only include the correct users. From there you can sum on the collection summing all the data entries, but do it with the closure approach. Where you can fetch the data from the data structure, to call another sum().

$reportData = collect(json_decode($report))->where('customer_id', $report->customer_id);

$reportData->sum(function ($item) {
    return collect($item->data->body)->sum();
});

This is simply an exercise in knowing and understanding collections methods and how to utilize them in a non standard data structure.

Upvotes: 0

Related Questions