How to $lookup using multiple localFields referencing multiple foreignFields

Working with Data
#1

Hi friends,

I am doing aggregation on $lookup on three collections.
The question is does mongo supports more than 1 field(more than one equality).
In my case I have three localFields and three foreignFields?
If it supports, how to achieve more than on equality condition using $lookup.

db.test.aggregate([
	{ $lookup : {
		from: "grl",           
		localField: "subGroup",           
		localField:  “date",	
	    localFiled: "currency"
		foreignField:  “Group",  
		foreignField:  “s_date", 
        foreignFiled: "currency"        
		as: "data"   	  
	} }
])

How to change my $lookup aggregation so that i have get combined records from all three collections

#2

Hi @Murali_Muppireddy,

Take a look at the Specify Multiple Join Conditions with $lookup section of the documentation.

Is this a different request where you want to join documents from three different collections, or is that a typo? To do a three collection join, you would need to perform multiple $lookup stages.

#3

Hi @Doug_Duncan, this is part of request… where I have to do join documents from three different collections, not sure how to do multiple $lookup stages…plz point me if there is some reference matching to my scenario.
In all three collections there are 3 common(same ) fields, based on equality matching on these three columns, I need to merger all three collections to one.

three collections, those i want merge into one based on1st three fields from each collection.
Co2

col1
col1796×242 5.72 KB
Col3

#4

Hi @Murali_Muppireddy,

For this you just follow the first $lookup stage with a second one.

db.test650.aggregate([
    {
        "$lookup": {
            "from": "test750",
            "localField": "Z",
            "foreignField": "Z",
            "as": "750joined"
        }
    },
    {
        "$lookup": {
            "from": "test850", 
            "localField": "X", 
            "foreignField": "X", 
            "as": "850joined"
        }
    }
])

This will will join the test650 and test750 collections together on the shared Z field. The fields for test650 will be in the top level document while the fields for test750 will be nested in an array field called 750joined. These results are then joined to data from test850 on the shared X field. The data from test850 will be nested in an array field called test850.

From what I remember in another post, you were joining collections on X, Y_DT and Z. You would use similar methods to the above, but change the $lookup to match how you joined on multiple fields.

1 Like
#5

Hi @Doug_Duncan, The above query is giving the one setup of results, I need to apply two more lookups on another local fields from the collection test650 and on query fields 750joined and on 850joined. As said each collection has 3 three common fields(local fields) on which I have to do grouping, with the above query i got one setup of results, now I have to work with other two local fields on return array fields? is it possible and how to do it?

#6

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