Combine geoQuery with rating aggregation-CodePudding

I want to get all objects in a radius and also for each single of those objects their average rating and total ratings. I've got both queries working but I'm looking to combine these 2 into one.

LocationSchema

const LocationObject = new Schema({
    name: String
    location: {
        type: {
          type: String,
          enum: ['Point'],
          default: 'Point',
          required: true
        },
        coordinates: {
          type: [Number],
          required: true
        }
    }
})

ratingSchema

const Rating = new Schema({
    locationObject: { type: Schema.Types.ObjectId, ref: 'LocationObject' },
    average: Number,
})

locationQuery

const objects = await LocationObject.find({
        location: {
            $geoWithin: {
                $centerSphere: [[lon, lat, radius] 
            }
        }
    })

RatingAggregation for single LocationObject

const result = await Rating.aggregate([
    { 
      "$match": {
        "locationObject": objectID
      }
    },
    {
      "$facet": {
        "numbers": [
          {
          "$group": {
            "_id": null,
            "totalRating": {
              "$sum": "$average"
            },
            "totalItemCount": {
              "$sum": 1.0
            }
          }
        }
        ],
      }
    },
    {
      "$unwind": "$numbers"
    },
    {
      "$project": {
        "_id": null,
        "avgRating": {"$divide": ["$numbers.totalRating", "$numbers.totalItemCount"]},
        "totalRatings":  "$numbers.totalItemCount"
      }
    }
  ])

The final result should return an array with locationObjects which each has an average and totalRatings added.

mongo playground: https://mongoplayground.net/p/JGuJtB5bZV4

Expected result

[
  {
    name: String,
    location: {
      coordinates: [Number, Number],
    },
    avgRating: Number,
    totalRatings: Number
  },
  {
    name: String,
    location: {
      coordinates: [Number, Number],
    }
  }
]

CodePudding user response：

As per your latest playground, you could achieve using this

db.locationObject.aggregate([
  {
    "$match": {
      "location": {
        "$geoWithin": {
          "$centerSphere": [
            [
              6.064953,
              52.531348
            ],
            0.0012
          ]
        }
      }
    }
  },
  {
    "$lookup": { //You need to bring both the collection data together
      "from": "Rating",
      "localField": "_id",
      "foreignField": "locationObject",
      "as": "locRatings"
    }
  },
  {
    $unwind: "$locRatings"
  },
  {
    "$group": { //you can simplify the other pipelines
      "_id": "$_id",
      "field": {
        "$avg": "$locRatings.average"
      },
      "totalItemCount": {
        "$sum": 1.0
      }
    }
  }
])

To preserve the document fields, you need to use accumulators as in this playground

{
    "$group": {
      "_id": "$_id",
      "field": {
        "$avg": "$locRatings.average"
      },
      "totalItemCount": {
        "$sum": 1.0
      },
      "locations": {
        "$addToSet": "$location"
      }
    }
  }

you can keep empty/null arrays in unwind stage as below

playground

{
    $unwind: {
      "path": "$locRatings",
      "preserveNullAndEmptyArrays": true
    }
  },

You can add a project stage to ignore null values if needed.