MongoDB geoNear查询：如何仅返回用户最新且符合距离条件的位置？

我有一个MongoDB集合，每30秒收集一次所有用户的地理位置数据，需求是查询距离指定点的用户最新位置，忽略同一用户的旧记录，同时仅保留最新位置符合距离条件的结果。

集合文档示例 #

[
  {
    "_id": "id1",
    "purpose": "tracking",
    "user": "User1",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.11892896428128, 22.31833064059432]
    },
    "createdAt": ISODate("2023-05-08T10:08:15.049Z"),
    "updatedAt": ISODate("2023-05-08T10:08:15.049Z")
  },
  {
    "_id": "id2",
    "purpose": "tracking",
    "user": "User1",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.09304942391326, 22.321831847174654]
    },
    "createdAt": ISODate("2023-05-08T10:09:43.828Z"),
    "updatedAt": ISODate("2023-05-08T10:09:43.828Z")
  },
  {
    "_id": "id3",
    "purpose": "tracking",
    "user": "User2",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.14348330367717, 22.280082608116277]
    },
    "createdAt": ISODate("2023-05-08T10:12:30.670Z"),
    "updatedAt": ISODate("2023-05-08T10:12:30.670Z")
  },
  {
    "_id": "id4",
    "purpose": "tracking",
    "user": "User2",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.1542041657593, 22.27487612032671]
    },
    "createdAt": ISODate("2023-05-08T10:13:10.224Z"),
    "updatedAt": ISODate("2023-05-08T10:13:10.224Z")
  }
]

距离数据 #

各位置到指定原点[73.1528821581882, 22.29557552397352]的距离：

id1 - 3123
id2 - 5864
id3 - 3528
id4 - 2267

当前使用的聚合查询 #

DBModel.aggregate([
  {
    $geoNear: {
      near: { type: "Point", coordinates: [73.1528821581882, 22.29557552397352] },
      key: "geoLocation",
      distanceField: "distanceCalculated",
      minDistance: 0,
      maxDistance: 3000,
      query: { purpose: "tracking" },
      spherical: true,
    },
  },
  {
    $sort: {
      user: 1,
      createdAt: -1,
    },
  },
  {
    $group: {
      _id: "$user",
      geoLocation: {
        $first: "$geoLocation",
      },
      createdAt: {
        $first: "$createdAt",
      },
    },
  },
])

当前查询结果 #

[
  {
    "_id": "id1",
    "purpose": "tracking",
    "user": "User1",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.11892896428128, 22.31833064059432]
    },
    "createdAt": ISODate("2023-05-08T10:08:15.049Z"),
    "updatedAt": ISODate("2023-05-08T10:08:15.049Z"),
    "distanceCalculated": 3123
  },
  {
    "_id": "id4",
    "purpose": "tracking",
    "user": "User2",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.1542041657593, 22.27487612032671]
    },
    "createdAt": ISODate("2023-05-08T10:13:10.224Z"),
    "updatedAt": ISODate("2023-05-08T10:13:10.224Z"),
    "distanceCalculated": 2267
  }
]

期望结果 #

仅保留最新位置符合距离条件的用户：

[
  {
    "_id": "id4",
    "purpose": "tracking",
    "user": "User2",
    "geoLocation": {
      "type": "Point",
      "coordinates": [73.1542041657593, 22.27487612032671]
    },
    "createdAt": ISODate("2023-05-08T10:13:10.224Z"),
    "updatedAt": ISODate("2023-05-08T10:13:10.224Z"),
    "distanceCalculated": 2267
  }
]

问题分析 #

当前查询的逻辑错误在于：先通过$geoNear筛选出所有符合距离条件的历史记录，再按用户分组取最新的记录。但User1的最新记录id2不在距离范围内，旧记录id1在范围内，导致分组后取到了User1的旧记录，不符合“仅保留最新位置符合距离条件”的需求。

解决方案 #

方案一：先获取每个用户的最新记录，再筛选距离 #

先通过分组拿到每个用户的最新位置，再计算该位置的距离并过滤，确保只保留最新位置在范围内的用户：

DBModel.aggregate([
  // 按用户分组前先排序，确保每个用户的第一条是最新记录
  {
    $sort: {
      user: 1,
      createdAt: -1
    }
  },
  // 分组获取每个用户的最新完整记录
  {
    $group: {
      _id: "$user",
      latestRecord: { $first: "$$ROOT" }
    }
  },
  // 将最新记录作为根文档
  { $replaceRoot: { newRoot: "$latestRecord" } },
  // 计算最新位置到指定点的距离
  {
    $set: {
      distanceCalculated: {
        $geoDistance: {
          near: { type: "Point", coordinates: [73.1528821581882, 22.29557552397352] },
          distanceField: "geoLocation",
          spherical: true
        }
      }
    }
  },
  // 过滤距离符合条件且用途为tracking的记录
  {
    $match: {
      purpose: "tracking",
      distanceCalculated: { $lte: 3000 }
    }
  }
])

方案二：通过$lookup关联获取最新记录并筛选 #

先找到每个用户的最新时间，再关联自身集合匹配最新记录，同时在关联管道内完成距离计算与过滤：

DBModel.aggregate([
  // 获取每个用户的最新创建时间
  {
    $group: {
      _id: "$user",
      maxCreatedAt: { $max: "$createdAt" }
    }
  },
  // 关联自身集合，获取对应用户的最新记录并过滤距离
  {
    $lookup: {
      from: "your_collection_name", // 替换为你的实际集合名称
      let: { userId: "$_id", maxTime: "$maxCreatedAt" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$user", "$$userId"] },
                { $eq: ["$createdAt", "$$maxTime"] },
                { $eq: ["$purpose", "tracking"] }
              ]
            }
          }
        },
        // 计算距离
        {
          $set: {
            distanceCalculated: {
              $geoDistance: {
                near: { type: "Point", coordinates: [73.1528821581882, 22.29557552397352] },
                distanceField: "geoLocation",
                spherical: true
              }
            }
          }
        },
        // 过滤符合距离条件的记录
        { $match: { distanceCalculated: { $lte: 3000 } } }
      ],
      as: "latestRecords"
    }
  },
  // 展开结果数组
  { $unwind: "$latestRecords" },
  // 将最新记录作为根文档
  { $replaceRoot: { newRoot: "$latestRecords" } }
])

内容的提问来源于stack exchange，提问作者Durvesh Parmar