I'm making an aggregation and making $lookup to get a collection where the location is .

The problem is : I cant do a $geoNear after the first stage of the pipeline. So I tried $nearSphere at the end of pipeline but it didn't work.

Any other way ? If I could get the distance I could sort the array after the aggregation.

The only solution I found, is : after the aggregation, I calculate the distance from the same point for each document and sort them but I'm not sure sure it is the most optimized !

Here is the aggregation I tried:

MyCollection.aggregate([
  {
    $match: {
      job: 'professor'
    }
  },
  {
    $lookup: {
      from: "otherCollection",
      localField: "_id",
      foreignField: "_id",
      as: "otherCollection"
    }
  },
  {
    $unwind: {
      path: "otherCollection",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $addFields: {
      location: '$otherCollection.location'
    }
  },
  {
    $nearSphere: {
      $geometry: {
        type: "Point",
        coordinates: [lng, lat] // Those avriables are passed in arguments in the function before
      },
      $maxDistance: radius * 1000,
      // I tried to add a $key field to get $otherCollection._id but it doesn't worked
    }

  }
], function(err, data) {
  if (err) errorCbk(err)

  successCbk(data)
});

Thanks.

CodePudding user response：

You can use $sort and make any calculation you want on it https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort/

CodePudding user response：

You could "invert" the query by first using "$geoNear" in otherCollection and then "$lookup" into MyCollection.

Something like this.

db.otherCollection.aggregate([
  {
    "$geoNear": {
      "near": {
        "type": "Point",
        "coordinates": [ -46.0, -68.0 ]
      },
      "maxDistance": 2000000,
      "distanceField": "dist.calculated"
    }
  },
  {
    "$lookup": {
      "from": "MyCollection",
      "localField": "_id",
      "foreignField": "_id",
      "pipeline": [
        { "$match": { "job": "professor" } }
      ],
      "as": "nearProfs"
    }
  },
  {
    "$match": {
      "$expr": {
        "$gt": [ { "$size": "$nearProfs" }, 0 ]
      }
    }
  }
])

Try it with fake data on mongoplayground.net.