mongodb - 如何在 mongodb 聚合中使用 $geoNear 和 $lookup

Question

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

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
}

Outlet数据模型

{
    "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"
    }
  ]
}

Answer 1

1) 您需要在现场位置的导出集合上有 2dsphare 索引。

你可以使用:

db.outlet.createIndex({位置:“2dsphere”})

2) 您必须对导出集合运行聚合，因为它包含位置字段，并且您只能使用 $geoNear 作为管道的第一阶段。

你的查询看起来像

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

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

3) 然后您可以使用 $lookup 运算符组合您商店中的优惠。

您的完整查询看起来像

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 } }
])