How to use lookup when both the tables have the same key names?

Working with Data
1

I have two collections and both of them have the same key names platform, channel and month. When I am using lookup, the let step is taking the variables from the first collection and not the collection b. Using the dot operator also does not work.

Another problem is that month and year operators are not working in let.

How do I fix these?

{
    $lookup: {
        from: "b",
        let: { 
          rev_platform: "$b.platform", 
          rev_channel: "$b.channel", 
          rev_month: {$month:'$b.month'}, 
          rev_year: {$year:'$b.month'}
        },
        pipeline: [
            { $match:
            { $expr:
                { $and:
                    [
                    { $eq: [ "$platform",  "$$rev_platform" ] },
                    { $eq: [ "$channel", "$$rev_channel" ] },
                    { $eq: [ "$month", "$$rev_month" ] },
                    { $eq: [ "$year", "$$rev_year" ] },
                    ]
                }
            }
            },
        ],
        as: "fromB"
    }
},
2

It is the other way around. In $let, you defined variable using expression on source documents. If you think about, b`s documents are not yet there so you can’t refer to them; they start to exist when the $match inside the pipeline is evaluated. So you simply have to replace

with

let: { 
          rev_platform: "$platform",
          rev_channel: "$channel", 
          rev_month: {$month:'$month'}, 
          rev_year: {$year:'$month'}
        },

The $expr stays the same because the $platform is b’s platform.

See https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/ for more information. For your case, there is an example with orders and warehouse collections.

2 Likes
3

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.