node.js - 根据条件加入mongodb集合

Question

这个问题已经有答案了:

Conditional $lookup in MongoDB? (1 个回答)

已关闭 3 年前。

我在 MongoDB 中有两个集合，想要根据某些条件连接这两个集合。

我想加入“订单”和“订单状态”表，以获取分配给“123”且状态为“就绪”的所有订单

订单

{
 "_id":"1",
 "name": "Fridge",
 "assignee": "123"
},
{
 "_id":"2",
 "name": "TV",
 "assignee": "567"
},
{
 "_id":"3",
 "name": "Music system",
 "assignee": "123"
}

订单状态

{
 "_id":"1",
 "status": "ready",
 "orderId": "1"
},
{
 "_id":"2",
 "status": "cancelled",
 "orderId": "2"
},
{
 "_id":"3",
 "status": "cancelled",
 "orderId": "3"
}

受让人

{
 "_id":"123",
 "name": "Jak"
}
{
 "_id":"567",
 "name": "Mac"
}

我想加入“订单”和“订单状态”表，以获取分配给“123”且状态为“就绪”的所有订单

预计最终结果为

[
{
 "_id":"1",
 "name": "Fridge",
 "assignee": "123",
 "status": {
  "_id":"1",
 "status": "ready",
 "orderId": "1"
 }
}
]

尝试了以下方法，但如何通过查找检查另一个表中的订单状态

const resultObject = orders.aggregate([
  { $match : {assignee: Objectid('123')} },
  {
    $lookup: {
      from: 'user-status',
      localField: 'assignee',
      foreignField : '_id',
      as : 'assignee'
    }
  },
  {
    $unwind: '$assignee'
  }
]);

Answer 1

首先需要使用match按照"assignee": "123"进行过滤，然后需要查找order-status，匹配"orderStatus.status": "ready"。

const resultObject = orders.aggregate([
  {
    $match: {
      assignee: "123"
    }
  },
  {
    $lookup: {
      from: "order-status",
      localField: "_id",
      foreignField: "orderId",
      as: "statuses"
    }
  },
  {
    $match: {
      "statuses.status": "ready"
    }
  },
  {
    $project: {
      id: "_id",
      name: "$name",
      assignee: "$assignee",
      status: {
        $arrayElemAt: ["$statuses", 0]
      }
    }
  }
]);

这将给出如下结果:

[
  {
    "_id": "1",
    "assignee": "123",
    "name": "Fridge",
    "status": {
      "_id": "1",
      "orderId": "1",
      "status": "ready"
    }
  }
]

Playground