Lookup on different table

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Can we apply lookup on different collections i.e.
A collection (on accountId field ) lookup B collection(on accountId field) lookup C collection (on trx field)

let me clarify this i want to make a lookup on

1. A(on accountId field) -> B (on accountId field)
2. B(on trx field)->C (on trx field)

Is that possible in mongoDB to apply lookup in a same query on different collections as mentioned above?
Thanks in advance.

[Lauren_Schaefer]

Yes, you can perform $lookup more than once in an aggregation. Here is a pipeline I built to be run on Collection b:

[{
    $lookup: {
        from: 'a',
        localField: 'accountId',
        foreignField: 'accountId',
        as: 'infoFromA'
    }
}, {
    $lookup: {
        from: 'c',
        localField: 'trx',
        foreignField: 'trx',
        as: 'infoFromC'
    }
}]

I’m including a screenshot from Atlas, so you can see what’s happening in each stage.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

@Lauren_Schaefer this is not the requirement might be i was unable to define my question properly. my question is that we all know that we can join many collections based on a field. i just want to join Collection A to collection B (which is a piece of cake) the next tricky thing is that i want to add join on Collection C with Collection B in the same query is that possible to do so in mongodb?

1- Collection A joins Collection B joins Collection C (this is what we all know)
2- Collection A joins Collections B, Collection B joins Collection C (in the same query)

Point 2 is required.

[Lauren_Schaefer]

Hmm…I’m not sure what you mean. I can run this in a single query:

db.b.aggregate([{
    $lookup: {
        from: 'a',
        localField: 'accountId',
        foreignField: 'accountId',
        as: 'infoFromA'
    }
}, {
    $lookup: {
        from: 'c',
        localField: 'trx',
        foreignField: 'trx',
        as: 'infoFromC'
    }
}])

Below is the output:

{ "_id" : ObjectId("5f5a12bf931c05e9b75e0f87"), "accountId" : "1", "hi" : "there", "trx" : "one",    "infoFromA" : [ { "_id" : ObjectId("5f5a12a0931c05e9b75e0f85"), "accountId" : "1", "something" : "else" } ], "infoFromC" : [ { "_id" : ObjectId("5f5a12f1931c05e9b75e0f89"), "trx" : "one", "waz" : "up" } ] }
{ "_id" : ObjectId("5f5a12c8931c05e9b75e0f88"), "accountId" : "3", "hello" : "again", "trx" : "one", "infoFromA" : [ ], "infoFromC" : [ { "_id" : ObjectId("5f5a12f1931c05e9b75e0f89"), "trx" : "one", "waz" : "up" } ] }

If that is not what you mean, please provide sample documents for each collection as well as the output you are looking for.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

@Lauren_Schaefer you are using Collection B as parent collection for aggregation but there will be 5 more joins with Collection A so it should be at the top.

[Lauren_Schaefer]

The joins are happening on the the fields you indicated in your original question:

1. A(on accountId field) -> B (on accountId field)
2. B(on trx field)->C (on trx field)

I don’t think I fully understand what you are trying to do. Please provide sample documents for each collection as well as the output you are looking for.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

db.a.aggregate([{
$lookup: {
from: ‘b’,
localField: ‘accountId’,
foreignField: ‘accountId’,
as: ‘infoFromB’
}
}, {
$lookup: {
from: ‘c’,
localField: ‘b.trx’,
foreignField: ‘trx’,
as: ‘infoFromC’
}

}])

this is the solution.