如何在MongoDB $ lookup中使用变量 [英] How to use variables with MongoDB $lookup

问题描述

假设我有3个收藏集，cars，bikes，vehicles.

Let's say I have 3 collections, cars, bikes, vehicles.

cars 集合为:

{
  {
    "_id": "car1",
    "carBrand": "Audi",
    "color": "blue"
  },
  {
    "_id": "car2",
    "carBrand": "BMW",
    "color": "white"
  }
}

bikes 集合为:

{
  {
    "_id": "bike1",
    "bikeBrand": "Audi",
    "color": "red"
  },
  {
    "_id": "bike2",
    "carBrand": "BMW",
    "color": "white"
  }
}

和 vehicles 集合实际上只是引用cars和bikes集合为

and the vehicles collection actually just has references to cars and bikes collections as

{
  {
    "_id": "vehicle1",
    "vehicleType": "cars",
    "vehicleId": "car1"
  },
  {
    "_id": "vehicle2",
    "vehicleType": "cars",
    "vehicleId": "car2"
  },
  {
    "_id": "vehicle3",
    "vehicleType": "bikes",
    "vehicleId": "bike1"
  },
  {
    "_id": "vehicle4",
    "vehicleType": "bikes",
    "vehicleId": "bike2"
  },

}

我想将vehicles集合与cars和bikes集合一起加入.我试图将"$vehicleType"设置为$lookup的from字段的变量.但是，它没有按我预期的那样工作.它只是不加入表.没有错误.

I'd like to join the vehicles collection with cars and bikes collections. I tried to set "$vehicleType" as a variable to $lookup's from field. However it does not work as I expected. It simply does not join the tables. No errors.

db.collection.aggregate([{

  $lookup: {
      from: "$vehicleType",
      localField: "vehicleId",
      foreignField: "_id",
      as: "vehicleDetails"
  }

}]);

我原本希望得到这样的结果

I was expecting to have a result something like this

{
  {
    "_id": "vehicle1",
    "vehicleType": "cars",
    "vehicleId": "car1",
    "vehicleDetails": {    
      "_id": "car1",
      "carBrand": "Audi",
      "color": ""
    }
  },
  {
    "_id": "vehicle2",
    "vehicleType": "cars",
    "vehicleId": "car2",
    "vehicleDetails":    {
      "_id": "car2",
      "carBrand": "BMW",
      "color": "white"

  },
  {
    "_id": "vehicle3",
    "vehicleType": "bikes",
    "vehicleId": "bike1",
    "vehicleDetails":    {
      "_id": "bike1",
      "bikeBrand": "Audi",
      "color": "red"
    }
  },
  {
    "_id": "vehicle4",
    "vehicleType": "bikes",
    "vehicleId": "bike2",
    "vehicleDetails":    {
      "_id": "bike2",
      "carBrand": "BMW",
      "color": "white"
    }
  },

}    

推荐答案

如果汽车和自行车没有通用ID，则可以按顺序在单独的数组中查找，然后将它们与

If cars and bikes don't have common IDs, you can sequentially lookup in separate arrays, then combine them with $setUnion:

db.vehicles.aggregate([
  {$lookup: {
      from: "cars",
      localField: "vehicleId",
      foreignField: "_id",
      as: "carDetails"
  }},
  {$lookup: {
      from: "bikes",
      localField: "vehicleId",
      foreignField: "_id",
      as: "bikeDetails"
  }},
  {$project: {
     vehicleType: 1,
     vehicleId: 1,      
     vehicleDetails:{$setUnion: [ "$carDetails", "$bikeDetails" ]}
  }},
  {$project: {
      carDetails:0,
      bikeDetails:0,
  }}
]);

否则，您将需要使用 $ facet 进行过滤查找前按类型划分的车辆:

Otherwise you will need to use $facet to filter vehicles by type before lookup:

db.vehicles.aggregate([
   {
     $facet: {
         "cars": [
            {$match: {"vehicleType": "cars"}},
            {$lookup: {
               from: "cars",
               localField: "vehicleId",
               foreignField: "_id",
               as: "vehicleDetails"
             }},
         ],
         "bikes": [
            {$match: {"vehicleType": "bikes"}},
            {$lookup: {
               from: "bikes",
               localField: "vehicleId",
               foreignField: "_id",
               as: "vehicleDetails"
             }}
         ]
     }
   },
   {$project: {all: {$setUnion: ["$cars", "$bikes"]}}},
   {$unwind: "$all"},
   {$replaceRoot: { newRoot: "$all" }}
])

这篇关于如何在MongoDB $ lookup中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！