mongodb - 在 mongodb 中加入多个表以从另一个表中获取 id 值数组

Question

我正在尝试加入两个表，并使用 mongodb 根据从一个表到另一个表的 id 数组获取一些额外的数据。一个与“用户”相关的集合，另一个是“订单”。用户集合包含与订单相关的 id 数组，订单表包含订单名称和其他数据。我需要列出所有用户的所有订单名称和数据以及用户详细信息。

例如：

User Collection:

User = [{
_id: ObjectId,
userId: 1,
userName: "User1",
OrderIds: [{oId: 1},{oId:3}]
},
{
_id: ObjectId,
userId: 2,
userName: "User2",
OrderIds: [{oId: 2},{oId:3}]
}]
--------------------------------------------
Order Collection:

Order = [{
_id: ObjectId,
orderId: 1
orderName: "Pen"
},
{
_id: ObjectId,
orderId: 2
orderName: "Book"
},
{
_id: ObjectId,
orderId: 3
orderName: "Chair"
}]
---------------------------------------------------

Desired Output:
-----------------------------------------------------
Output = [{
_id: ObjectId,
userId: 1,
userName: "User1",
OrderIds: [{oId: 1,oName: "Pen"},{oId:3,oName: "Chair"}]
},
{
_id: ObjectId,
userId: 2,
userName: "User2",
OrderIds: [{oId: 2,oName: "Book"},{oId:3,oName: "Chair"}]
}]

请任何人有想法，如何做到这一点？

标签： mongodb

Answer 1

使用$lookup

db.users.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "OrderIds.oId",
      "foreignField": "orderId",
      "as": "OrderIds"
    }
  }
])

mongoplayground