代码之家 › 专栏 › 技术社区 › Vikas Valechha

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

aggregation-framework mongodb

Vikas Valechha · 技术社区 · 6 年前

在我的MongoDB聚合查询中,我使用$lookup加入提供集合使用出口收集但是,在我的“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
}

出口数据模型

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

1 回复 | 直到 6 年前

Waqas Noor 6 年前

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

您可以使用以下方法制作:

数据库。出口createIndex({位置:“2dsphere”})

2) 您必须对outlet集合运行聚合,因为它包含location字段,并且您只能将$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 } }
])