如何在MongoDB聚合中使用$lookup和$geoNear？

Question

在我的MongoDB聚合查询中，我使用$lookup将我的offers集合与outlet集合连接起来。但是，在我的“outlets”集合中，我有一个名为location的字段，我希望查询结果按照该位置从最近到最远的顺序排序。那么，如何在$lookup中使用$geoNear？任何帮助都将不胜感激。以下是我的查询：

db.offers.aggregate([
  {
    $geoNear: {
      near: {
        type: "Points",
        coordinates: [
          22,
          77
        ]
      },
      distanceField: "distance",
      maxDistance: 5000,
      spherical: true
    }
  },
  {
    $match: {
      $and: [
        {
          'totalDiscount': {
            $gt: 40
          }
        },
        {
          'totalDiscount': {
            $lt: 60
          }
        }
      ]
    }
  },
  {
    $unwind: "$storeUuid"
  },
  {
    $lookup: {
      from: "outlets",
      localField: "storeUuid",
      foreignField: "uuid",
      as: "store"
    }
  },
  {
    $project: {
      _id: 0,
      location1: {
        $arrayElemAt: [
          "$store.location",
          0
        ]
      }
    }
  },
  {
    $addFields: {
      'location.latitude': {
        $ifNull: [
          {
            $arrayElemAt: [
              "$location1.coordinates",
              1
            ]
          },
          0
        ]
      },
      'location.longitude': {
        $ifNull: [
          {
            $arrayElemAt: [
              "$location1.coordinates",
              0
            ]
          },
          0
        ]
      }
    }
  },
  {
    $sort: {
      location: 1
    }
  }
])

提供数据模型

{
    "offerId": "6e9d595a-16ad-4c6c-93d9-a7edc2bbb56f",
    "brandUuid": [
        "5b198438-8b4c-46f0-8cc2-6a938cb41d8e"
    ],
    "storeUuid": [
        "33ca653e-2af0-4728-b4a0-1178565c2b40",
        "1b383916-8856-4f5a-8761-4bd4585e1d71"
    ],
    "totalDiscount": 50
}

出口数据模型

{
    "uuid": "20389cc1-2791-4d7b-a603-75b7abd6d48a",
    "location": {
        "type": "Point",
        "coordinates": [
            77.6504768,
            12.9176082
        ]
    }
},

编辑：基于Waqas Noor的答案

实际结果

{
  "offers": [
    {
      "uuid": "33ca653e-2af0-4728-b4a0-1178565c2b40",
      "distance": 2780.7979952350124,
      "offerId": "6e9d595a-16ad-4c6c-93d9-a7edc2bbb56f"
    },
    {
      "uuid": "b4768792-a927-4d65-91a3-8ad67ad217b2",
      "distance": 3930.1660094190306,
      "offerId": "4f71fe98-cb43-4134-b360-b32017981de1"
    },
    {
      "uuid": "1dbac2d2-b326-4d6d-8d74-9df99f35f542",
      "distance": 3973.3702922423313,
      "offerId": "070b916c-dd4d-42b4-b886-74318f576ffb"
    },
    {
      "uuid": "20389cc1-2791-4d7b-a603-75b7abd6d48a",
      "distance": 4107.770111767324,
      "offerId": "0f037c18-a58f-4b03-b0f4-db8e2d971b74"
    },
    {
      "uuid": "20389cc1-2791-4d7b-a603-75b7abd6d48a",
      "distance": 4107.770111767324,
      "offerId": "070b916c-dd4d-42b4-b886-74318f576ffb"
    },
    {
      "uuid": "2f968cfa-1bf1-4344-bc73-998f4974f58a",
      "distance": 4165.187832520325,
      "offerId": "4f71fe98-cb43-4134-b360-b32017981de1"
    },
    {
      "uuid": "3cc1461f-f29b-4744-a540-69d24ebb98a8",
      "distance": 4262.636071210964,
      "offerId": "0f037c18-a58f-4b03-b0f4-db8e2d971b74"
    },
    {
      "uuid": "3cc1461f-f29b-4744-a540-69d24ebb98a8",
      "distance": 4262.636071210964,
      "offerId": "070b916c-dd4d-42b4-b886-74318f576ffb"
    },
    {
      "uuid": "1b383916-8856-4f5a-8761-4bd4585e1d71",
      "distance": 4361.786323018647,
      "offerId": "6e9d595a-16ad-4c6c-93d9-a7edc2bbb56f"
    },
    {
      "uuid": "7af0e1f8-d4d6-4700-adea-1df07a029f56",
      "distance": 4564.666204168865,
      "offerId": "8bbb5e27-89ff-417f-8312-f70e3911cb4c"
    }
  ]
}

预期结果

{
  "offers": [
    {
      "uuid": "33ca653e-2af0-4728-b4a0-1178565c2b40",
      "distance": 2780.7979952350124,
      "offerId": "6e9d595a-16ad-4c6c-93d9-a7edc2bbb56f"
    },
    {
      "uuid": "b4768792-a927-4d65-91a3-8ad67ad217b2",
      "distance": 3930.1660094190306,
      "offerId": "4f71fe98-cb43-4134-b360-b32017981de1"
    },
    {
      "uuid": "1dbac2d2-b326-4d6d-8d74-9df99f35f542",
      "distance": 3973.3702922423313,
      "offerId": "070b916c-dd4d-42b4-b886-74318f576ffb"
    },
    {
      "uuid": "20389cc1-2791-4d7b-a603-75b7abd6d48a",
      "distance": 4107.770111767324,
      "offerId": "0f037c18-a58f-4b03-b0f4-db8e2d971b74"
    },
    {
      "uuid": "2f968cfa-1bf1-4344-bc73-998f4974f58a",
      "distance": 4165.187832520325,
      "offerId": "4f71fe98-cb43-4134-b360-b32017981de1"
    },
    {
      "uuid": "3cc1461f-f29b-4744-a540-69d24ebb98a8",
      "distance": 4262.636071210964,
      "offerId": "0f037c18-a58f-4b03-b0f4-db8e2d971b74"
    },
    {
      "uuid": "1b383916-8856-4f5a-8761-4bd4585e1d71",
      "distance": 4361.786323018647,
      "offerId": "6e9d595a-16ad-4c6c-93d9-a7edc2bbb56f"
    },
    {
      "uuid": "7af0e1f8-d4d6-4700-adea-1df07a029f56",
      "distance": 4564.666204168865,
      "offerId": "8bbb5e27-89ff-417f-8312-f70e3911cb4c"
    }
  ]
}

- Vikas Valechha

---

发布你的数据集到两个提到的集合中。 - Rahul Raj

我已经添加了数据集@RahulRaj。 - Vikas Valechha

你有什么可以帮助我的吗，@RahulRaj？ - Vikas Valechha

请你能否从示例数据集中删除所有无关字段，并提供一个输出示例？ - cbartosiak

Answer 1

1) 您需要在出口集合上的位置字段上拥有2dsphare索引。

您可以使用以下命令创建索引：

db.outlet.createIndex( {location : "2dsphere" } )

2) 由于出口集合包含位置字段，并且您只能将$ geoNear用作管道的第一阶段，因此必须在出口集合上运行聚合。

您的查询将如下所示：

db.outlet.aggregate([
    {
         $geoNear: {
            near: { type: "Point", coordinates: [ 77.6504768, 
                12.9176088] },
            distanceField: "distance",

            includeLocs: "location",
            spherical: true
         }
       }])

3) 然后您可以使用$lookup操作符在您的outlets中组合优惠。

您完整的查询将类似于：

db.outlet.aggregate([
  {
    $geoNear: {
      near: {
        type: "Point", coordinates: [77.6504768,
          12.9176088]
      },
      distanceField: "distance",

      includeLocs: "location",
      spherical: true
    }
  },
  { $project: { uuid: 1, distance: 1 } },
  {
    $lookup: {
      from: "offers",
      localField: "uuid",
      foreignField: "storeUuid",
      as: "offers"
    }
  },
  { $unwind: '$offers' },
  {
    $match: {
      'offers.totalDiscount': {
        $gt: 40,
        $lt: 60
      }
    }
  },
  { $sort: { distance: -1 } }
])

- Waqas Noor