I have one Order model in that i have Task subdocument. and other model of task invites in which there is a reference of task.

Now i want to retrieve all tasks with their invites available.

I have tried with normal lookup but getting invites in root but not with particular task.

order schema -

{_id: 1, tasks: [{_id: 1, name: "task1"}, {_id: 2, name: "task2"}]}

invite schema -

{_id: 1, task: 1, name: "Invite one"}

Query -

const order = await this.orderModel
      .aggregate([
        {
          $match: { _id: orderId },
        },
        {
          $lookup: {
            from: 'taskinvites',
            localField: 'tasks$._id',
            foreignField: 'invite',
            as: 'invites',
            pipeline: [
              {
                $match: {
                  $expr: {
                    $in: ['$status', [TaskInviteStatus.ACCEPTED, TaskInviteStatus.UNDER_REVIEW]],
                  },
                },
              },
            ],
          },
        },
        { $unwind: { path: '$invites', preserveNullAndEmptyArrays: true } },
        
      ])

CodePudding user response：

One option is using the $unwind before the $lookup:

db.orders.aggregate([
  {$match: {_id: 1}},
  {$unwind: {path: "$tasks", preserveNullAndEmptyArrays: true}},
  {$lookup: {
      from: "taskinvites",
      let: {taskId: "$tasks._id"},
      pipeline: [
        {$match: {
            $expr: {
              $and: [
                {$in: ["$status", ["ACCEPTED", "UNDER_REVIEW"]]},
                {$eq: ["$task", "$$taskId"]}
              ]
            }
          }
        }
      ],
      as: "invite"
    }
  }
])

See how it works on the playground example