Search code examples
mongodbmongodb-queryaggregation-frameworkmongodb-lookup

On MongoDb Aggregation lookup, does the let need special formating?


I am trying to use the MongoDB $lookup with the Uncorrelated Subqueries.
Using MongoDB 3.6.12 (support began on 3.6) https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries

The following pipeline step is working, however if I swap out the first "userB" with the second, no results are returned.

{
from: 'friendships',
let: { requestuser: ObjectId("5c0a9c37b2365a002367df79"), postuser: ObjectId("5c0820ea17a69b00231627be") },
pipeline: [
   { 
        $match : { 
            $or : [
                { 
                    "userA" : ObjectId("5c0820ea17a69b00231627be"), 
                    "userB" : ObjectId("5c0a9c37b2365a002367df79")
                     // "userB" : '$$requestuser'
                }, 
                { 
                    "userA" : ObjectId("5c0a9c37b2365a002367df79"), 
                    "userB" : ObjectId("5c0820ea17a69b00231627be")
                }
            ], 
            "accepted" : true
        }
    },
    {
   $project: {v: 'true'}
    }
 ],
as: "match"}

Results with hard coded ObjectId:

"match" : [
        {
            "_id" : ObjectId("5d6171dd319401001fd326bf"), 
            "v" : "true"
        }
    ]

Results using variable:

"match" : [

    ]

I feel like ObjectIds need special treatment. All the examples I could find are using simple variables like strings.

To verify the '$$requestUser' contained a value, I tested it on the projection:

"match" : [
        {
            "_id" : ObjectId("5d6171dd319401001fd326bf"), 
            "v" : ObjectId("5c0a9c37b2365a002367df79")
        }
    ]

Solution

  • When you use un co-related sub queires, you need to use $expr to pass a variable. You can try something like following.

    {
        $match: {
          $expr: {
            $or: [
                { 
                    $and:[
                      {
                        $eq: [ "userA", ObjectId("5c0820ea17a69b00231627be") ]
                      },
                      {
                        $eq: [ "userB", ObjectId("5c0a9c37b2365a002367df79") ]
                      },
                      {
                        $eq: [ "userB", "$$requestuser" ]
                      }
                    ]           
                },
                {
                    $and:[
                      {
                        $eq: [ "userA", ObjectId("5c0a9c37b2365a002367df79") ]
                      },
                      {
                        $eq: [ "userB", ObjectId("5c0820ea17a69b00231627be") ]
                      }
                    ]
                }
              
              
            ]
          },
          "accepted": true,
          
        }
      }
    

    I have created a sample demo to show how $expr works inside the lookup : Sample demo for Uncorrelated Subquery