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

[LiNo_Castro]

How do i join user with company that are primary to the user? see below for sample the data.

db.companies.insert([
   { "_id" : 4, "name" : "CompanyABC"},
   { "_id" : 5, "name" : "CompanyLMN"},
   { "_id" : 6, "name" : "CompanyXYZ"},
])

db.user.insert([
{
  "_id": 1,
  "name": "john",
  "companies": [
    {
      "_id": 4,
      "primary": true
    },
    {
      "_id": 5,
      "primary": false
    },
    {
      "_id": 6,
      "primary": false
    }
  ]
},
  "_id": 2,
  "name": "jane",
  "companies": [
    {
      "_id": 4,
      "primary": false
    },
    {
      "_id": 5,
      "primary": true
    },
    {
      "_id": 6,
      "primary": false
    }
  ]
},
])

I tried doing this below but it returned empty.

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

                            ]
                        ]
                    ],
                    'as' => 'actualCompanies',
                ]
}]

[steevej (Steeve Juneau)]

Can a user have more that one primary?

I would do that in 2 stages:

1. a $set stage that $reduce the user’s companies to the primary
2. do a normal $lookup using the field created in the $set stage above as localField

Should be easier to debug since you can stop after stage 1 and see if the $set field works correctly. Then it is a trivial $lookup.

[MaBeuLux88 (Maxime Beugnet)]

To keep things simple, I would do it like that.

[
  {
    '$addFields': {
      'companies': {
        '$filter': {
          'input': '$companies', 
          'as': 'item', 
          'cond': {
            '$eq': [
              '$$item.primary', true
            ]
          }
        }
      }
    }
  }, {
    '$lookup': {
      'from': 'companies', 
      'localField': 'companies._id', 
      'foreignField': '_id', 
      'as': 'companies'
    }
  }
]

There is probably a way to make it work with the subpipeline like you tried, but apparently I’m not good enough just yet.
The array isn’t helping.

Cheers,
Maxime.

[LiNo_Castro]

thanks Steevej, got the idea now. its pretty much like what MaBeuLux88 suggested below.
Didn’t think that way initially, it’s probably just that im still having the MySQL way of doing things right now though. =) The debugging by stage will really be handy idea for me going forward.

[LiNo_Castro]

@ MaBeuLux88 Thanks, this is how i ended up using.

[LiNo_Castro]

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…

[MaBeuLux88 (Maxime Beugnet)]

I got the same confusion when I tried! I’m glad you got it to work.