I am new in Mongodb and I´m trying to create a query which print all the combinations of points that were assigned to the accommodations and sort them by the number of accommodations that received these points. However when I execute this query, the $sum is always returning 0 despite the 3 fields are numeric values:

db.test.aggregate([
  {$addFields: {sumPoints: **{$sum: ["$lodging.reviews.cleanliness", "lodging.reviews.location", "lodging.reviews.food"]}**}},
  {$group: {
    _id: "$sumPoints",
    count: {$sum: 1}
  }},
  {$sort: {count: 1}},
  {$project: {_id: 0, count: 1, sumPoints: "$_id"}}
  ])

In the photo I show a document example. Document example Does anyone know what can be the problem?

I tried with that query and the result is just: { count: 5984, sumPoints: 0 }

because sumPoints is always returning 0.

CodePudding user response：

I think there are two problems. The first is that you are missing the dollar sign (to indicate that you want to access the fields) for the second and third items. But on top of that, it seems that $sum might not be able to add from different values in the array by itself? Summing sums seems to have worked:

  {
    "$addFields": {
      "sumPoints": {
        $sum: [
          {
            $sum: [
              "$lodging.reviews.cleanliness"
            ]
          },
          {
            $sum: [
              "$lodging.reviews.location"
            ]
          },
          {
            $sum: [
              "$lodging.reviews.food"
            ]
          }
        ]
      }
    }
  }

Playground example here

Alternatively, you can use the $reduce operator here:

  {
    "$addFields": {
      "sumPoints": {
        "$reduce": {
          "input": "$lodging.reviews",
          "initialValue": 0,
          "in": {
            $sum: [
              "$$value",
              "$$this.cleanliness",
              "$$this.location",
              "$$this.food"
            ]
          }
        }
      }
    }
  }

Playground example here

In the future please also provide the text for your sample documents (or, better yet, a playground example directly) so that it is easier to assist.