Finding multiple column average of the average of values in SQL json column

Question

Say I have a database with data in the following format

---

| id | name        | rating                                      |
-----------------------------------------------------------------|
| 1  | first       | {"looks": 20, "quality": 20, "quantity": 20}|
-----------------------------------------------------------------|
| 2  | second      | {"looks": 30, "quality": 40, "quantity": 20}|
------------------------------------------------------------------

First, I need to get the average of the rating of each item in the json object. For example, get average of looks, quality and quantity, for both item first and second. Item one would have an average rating of 20, and item second would have an average rating of 30. Then get the average of the average ratings of the two items, that would be of 20 and 30, which would give me a final result of 25.

I would like to implement this using the Laravel, Eloquent query builder, but I wouldn't mind getting this in raw sql format. I am using Postgres database.

I have found this answer but it only adresses json with single key and not in multiple rows. Any help would be appreciated.

Answer 1

You could use collections:

$collection = Model::get();
$ratings = $collection->map(function($i) {
    return collect(json_decode($i->rating))->avg();
});

And you want to add an average rating to result:

$collection = Model::get();
$collection->transform(function($i) {
    $i->average = collect(json_decode($i->rating))->avg();
    return $i;
});