How to do Aggregated $lookup in Mongodb on embedded collection of objects

Working with Data
6

Just before i close this topic, let me clarify that the solution i initially tried is actually legit and would work, i just messed up the ‘$expr’ part. see below for the corrected code:

                  '$expr' => [
                      '$and' => [
                          ['$eq' => ['$companies._id', '$company._id']],
                          ['$eq' => ['$companies.primary', true]]
                      ]
                  ]

I just replaced the ‘$_id’ with ‘$companies’ then the ‘$$company.primary’ with ‘$companies.primary’. I was just confused and forgot that i was joining companies to users, not users to company. see full code corrected code below:

db.users.aggregate( [{
                '$lookup' => [
                    'from' => 'companies',
                    'let' => ['company' => '$companies'],
                    'pipeline' => [
                        [
                            '$match' => [
                                '$expr' => [
                                    '$and' => [
                                        ['$eq' => ['$companies._id', '$$company._id']],
                                        ['$eq' => ['$companies.primary', true]]
                                    ]
                                ]
                            ]
                        ]
                    ],
                    'as' => 'actualCompanies',
                ]
}]

But moving forward i’d probably be using the staged method as suggested by @steevej. Thanks for the replies…

1 Like