How to use aggregate $lookup in nested data from mongoDB?

Question

I have collections like this:

// tasks

[
  {_id: '123', _user: '345', _solutions: ['567', '678'] }
]

// solutions

[
  {  _id: '567', _task: '123', _user: '345' },
  {  _id: '678', _task: '123', _user: '345' }
]

// users

[
 { _id: '345', name: 'Tom' }
]

With this code:

await db
    .collection<Task>('tasks')
    .aggregate([
      { $match: { _id: id } },
      {
        $lookup: {
          from: 'solutions',
          // I guess here should be pipeline  
          localField: '_solutions',
          foreignField: '_id',
          as: '_solutions',
        },
      },
      { $lookup: { from: 'users', localField: '_user', foreignField: '_id', as: '_user' } },
    ])

I have result as below:

task = {
  _id: 5e14e877fa42402079e38e44,
  _solutions: [
    {
      _id: 5e15022ccafcb4869c153e61,
      _task: 5e14e877fa42402079e38e44,
      _user: 5e007403fd4ca4f47df69913, <-- this should be userObject instead
    },
    {
      _id: 5e164f31cafcb4869c153e62,
      _task: 5e14e877fa42402079e38e44,
      _user: 5e007403fd4ca4f47df69913, <-- this should be userObject instead
    }
  ],
  _user: [
    {
      _id: 5e007403fd4ca4f47df69913,
      _solutions: [Array],
      _tasks: [Array],
    }
  ]
}

and I don't know how to $lookup into _solutions._user - so instead of objectId I will have exact user object.

mickl · Accepted Answer · 2020-05-22 00:11:56Z

You can run $lookup with custom pipeline for outer lookup and the regular one for users:

db.tasks.aggregate([
    {
        $match: {
            _id: "123"
        }
    },
    {
        $lookup: {
            from: "solutions",
            let: { solutions: "$_solutions" },
            pipeline: [
                { $match: { $expr: { $in: [ "$_id", "$$solutions" ] } } },
                {
                    $lookup: {
                        from: "users",
                        localField: "_user",
                        foreignField: "_id",
                        as: "_user"
                    }
                },
                { $unwind: "$_user" }
            ],
            as: "_solutions",      
        }    
    }  
])

Mongo Playground

Thank you very much mickl! It works just great right now how I expected!

Collectives™ on Stack Overflow

How to use aggregate $lookup in nested data from mongoDB?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related