How To Get Sum On Multiple Jsonb Values Laravel

Hi all,

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,
  }
}

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

bobbybouwmann

4 years ago

Level 88

You can do it by selecing a json object and use that to determine the score. However, I'm not sure if that works in Laravel. You will need a raw query for it.

You can find an example here: https://stackoverflow.com/questions/49490867/mysql-aggregated-sum-of-json-objects#answer-49561075

Why not just retrieve the 4 records from the database and sum the results yourself before storing a new value in the database? That should be fast enough for a cronjob ;)

1 like

Please or to participate in this conversation.