1

Using the Mongodb aggregate lookup function with pipeline does not return expected results using match with variable, only when not using the variable and give the _id will the match preform as expected.

I've setup a pipeline and my lookup stage uses a _id variable for the collection it's in to refer to the lookup collection and search through a list of associated items. When I use a known _id in the final match, I get the expected result, however, when I use the variable _id from the 'let' portion of the lookup, I get a blank array returned. I have tried to use the variable inside the ObjectId() function, using it as a string, or using it directly. I have also ran a projection to find that the variable is returning the correct id.

If I don't use the final match, all the results of the first two stages of the pipeline are appended to DAU, which does show that the join value is in the pipeline before the final $match.

Assoc_Certs is an array of ObjectIds like:

Assoc_Certs: { 0: ObjectId('1234'), 1: ObjectId('1235'), 2: ObjectId('1236')}

The certs._id is the ObjectId used for the variable and is found in the Assoc_Certs array.

db.certs.aggregate([
  {
    '$match': {
      'Active': true, 
      'Category': 'Certification'
    }
  }, {
    '$lookup': {
      'from': 'dau', 
      'let': {
        'objid': '$_id'
      }, 
      'pipeline': [
        {
          '$match': {
            'Alloc_Period_Info.e_date': {
              '$gte': new Date('Fri, 30 Aug 2019 06:00:00 GMT')
            }
          }
        }, {
          '$project': {
            'DAU_ID': 1, 
            'Alloc': {
              '$filter': {
                'input': '$Alloc_Period_Info', 
                'as': 'dt', 
                'cond': {
                  '$gte': [
                    '$$dt.e_date', new Date('Fri, 30 Aug 2019 06:00:00 GMT')
                  ]
                }
              }
            }, 
            'Assoc_Certs': 1
          }
        }, {
          '$match': {
            'Assoc_Certs': '$$objid'
          }
        }
      ], 
      'as': 'DAU'
    }
  }
]);

If I replace the final $match with the following stage the pipeline gives the expected result.

{'$match': {Assoc_Certs: ObjectId('5c1d5b465c31a417bdcb7249')}}

Using project the $$objid is ObjectId('5c1d5b465c31a417bdcb7249')

The expected result is the the pipeline lookup will join the Assoc_Certs to the variable passed in the $$objid and the DAU field will have only one record.

Heath
  • 153
  • 2
  • 2
  • 13
  • 1
    [Read the `$lookup` documentation](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#specify-multiple-join-conditions-with-lookup). You actually need `$expr` when doing this sort of use of the declared variable in a lower pipeline. Also really should be the **first** stage in order to not *brute force* over unnecessary documents in the "join". i.e `{ "$match": { 'Alloc_Period_Info.e_date': { '$gte': new Date('Fri, 30 Aug 2019 06:00:00 GMT') }, "$expr": { "$eq": [ "$Assoc_Certs", "$$objid" ] } }}` – Neil Lunn Aug 31 '19 at 00:31
  • I put this into Mongodb Compass to see the results and it didn't work. The direct match works fine still, but the $expr doesn't yield any results. `{ $match: { 'Alloc_Period_Info.e_date': { $gte: ISODate('2009-09-30T06:00:00.000+00:00') }, $expr: { $eq: [ '$Assoc_Certs', '$$objid' ] } } }` is what I tried. – Heath Sep 03 '19 at 15:20

0 Answers0