MongoDB: 如何使用lookup查询填充嵌套对象？

Question

我正在获取一组具有对其他集合的嵌套引用的记录列表，我希望使用MongoDb聚合查询查找该嵌套对象数组内的嵌套ObjectId。

数据库集合结构如下：

{
  subject: {type: String},
  body: {type: String},
  recipients: [{
    userId: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
    stutus: {type: String, enum: ['pending','accepted','rejected'], default:'pending'}
  }],
  sender: {type: mongoose.Schema.Types.ObjectId, ref: 'User'}
}

What I am expecting:

[{
  subject: 'Some subject here.',
  body: 'Lorem ipsum dolor emit set',
  recipients: [{
    userId: {firstName: 'John', lastName: 'Doe'},
    status: 'accepted'
  },{
    userId: {firstName: 'Jane', lastName: 'Doe'},
    status: 'accepted'
  }],
  sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
},{
  subject: 'Some subject here.',
  body: 'Lorem ipsum dolor emit set',
  recipients: [{
    userId: {firstName: 'Jane', lastName: 'Doe'},
    status: 'rejected'
  },{
    userId: {firstName: 'John', lastName: 'Doe'},
    status: 'accepted'
  }],
  sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
}]

任何形式的帮助都将不胜感激。

- Shivam Verma

Answer 1

• $unwind 对recipients数组进行拆分
• 使用用户集合$lookup查询recipients.userId
• $unwind 对recipients.userId数组进行拆分
• 使用用户集合$lookup查询sender
• $unwind 对sender数组进行拆分
• $group 按_id重新构建recipients数组

db.mails.aggregate([
  { $unwind: "$recipients" },
  {
    $lookup: {
      from: "users",
      localField: "recipients.userId",
      foreignField: "_id",
      as: "recipients.userId"
    }
  },
  { $unwind: "$recipients.userId" },
  {
    $lookup: {
      from: "users",
      localField: "sender",
      foreignField: "_id",
      as: "sender"
    }
  },
  { $unwind: "$sender" },
  {
    $group: {
      _id: "$_id",
      recipients: { $push: "$recipients" },
      subject: { $first: "$subject" },
      body: { $first: "$body" },
      sender: { $first: "$sender" }
    }
  }
])

游乐场

- turivishal

Answer 2

Try This:

db.emails.aggregate([
    { $unwind: "$recipients" },
    {
        $lookup: {
            from: "users",
            let: { userId: "$recipients.userId", status: "$recipients.stutus" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$userId"] }
                    }
                },
                {
                    $project: {
                        "_id": 0,
                        "userId": {
                            "firstName": "$firstName",
                            "lastName": "$lastName",
                        },
                        "status": "$$status"
                    }
                }
            ],
            as: "recipient"
        }
    },
    {
        $lookup: {
            from: "users",
            let: { userId: "$sender" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$userId"] }
                    }
                },
                {
                    $project: {
                        "_id": 0,
                        "firstName": 1,
                        "lastName": 1
                    }
                }
            ],
            as: "sender"
        }
    },
    {
        $group: {
            _id: "$_id",
            subject: { $first: "$subject" },
            body: { $first: "$body" },
            recipients: { $push: { $arrayElemAt: ["$recipient", 0] } },
            sender: { $first: { $arrayElemAt: ["$sender", 0] } }
        }
    }
]);

- Dheemanth Bhat