There are 3 collections for this application

1. users - all user details
2. partnership - user relation to each other
3. location - user geolocation

I have 1 and 2 working. The 3rd task is to sort the resuling users list by distance from a given coordinate.

Here is the mongo playground: https://mongoplayground.net/p/XELySm8KGpM

    db={
  users: [
    {
      _id: "abc",
      name: "abc",
      group: 1
    },
    {
      _id: "xyz",
      name: "xyyy",
      group: 1
    },
    {
      _id: "123",
      name: "yyy",
      group: 1
    },
    {
      _id: "rrr",
      name: "tttt",
      group: 1
    },
    {
      _id: "eee",
      name: "uuu",
      group: 1
    }
  ],
  partnership: [
    {
      _id: "abc_123",
      fromUser: "abc",
      toUser: "123"
    },
    {
      _id: "eee_rrr",
      fromUser: "eee",
      toUser: "rrr"
    },
    {
      _id: "rrr_abc",
      fromUser: "rrr",
      toUser: "abc"
    },
    {
      _id: "abc_rrr",
      fromUser: "abc",
      toUser: "rrr"
    },
    {
      _id: "xyz_rrr",
      fromUser: "xyz",
      toUser: "rrr"
    },
    {
      _id: "rrr_eee",
      fromUser: "rrr",
      toUser: "eee"
    },
    
  ],
  locations: [
    {
      _id: "123",
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      }
    },
    {
      _id: "rrr",
      location: {
        type: "Point",
        coordinates: [
          51.23,
          64.12
        ]
      }
    },
    {
      _id: "eee",
      location: {
        type: "Point",
        coordinates: [
          55.23,
          62.12
        ]
      }
    },
    {
      _id: "abc",
      location: {
        type: "Point",
        coordinates: [
          53.23,
          67.12
        ]
      }
    },
    
  ]
}

The following query by itself works. How to integrate it to the query in the playground?

  {
    $geoNear: {
       near: { type: "Point", coordinates: [ 41.99279 , -81.719296 ] },
       distanceField: "dist.calculated",
       spherical: true
    }
  },

CodePudding user response：

$geoNear can only be the first step in an aggregation pipeline, so you need to first sort by distance and then do all the other things. This means your schema is not very efficient for this.

One option is:

db.locations.aggregate([
   {$geoNear: {
       near: { type: "Point", coordinates: [ 41.99279 , -81.719296 ] },
       distanceField: "calculatedDist",
       spherical: true
    }
  },
  {$lookup: {
      from: "partnership",
      let: {user_id: "$_id"},
      pipeline: [
        {$match: {$expr: {
              $or: [
                {$and: [{$eq: ["$fromUser", "rrr"]}, {$eq: ["$toUser", "$$user_id"]}]},
                {$and: [{$eq: ["$toUser", "rrr"]}, {$eq: ["$fromUser",
                        "$$user_id"]}]},
                
              ]
        }}}
      ],
      as: "valid"
    }
  },
  {$match: {"valid.0": {$exists: true}}},
  {$lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user"
  }},
  {$project: {user: {$first: "$user"}, calculatedDist: 1}},
  {$sort: {calculatedDist: 1}},
  {$group: {_id: 0, users: {$push: "$user"}, count: {$sum: 1}}}
])

See how it works on the playground example