I've been trying to solve a joining problem between 3 different Collections in MongoDB. We have some confusions of "And" and "Or" at the time of these joins, could someone take a look at this query and assist in the solution?
db.getCollection('CarteiraMarketing').aggregate([{ '$match' : { 'CodigoGrupo' : '4000394460' } },
{ '$lookup' : { 'from' : 'PlantaFixaVozGtar',
'let' : { 'local_PlantaFixaVozGtar_CodigoGrupo' : '$CodigoGrupo' },
'pipeline' : [{ '$match' : { '$expr' : { '$and' : [{ '$eq' : ['$CodigoGrupo', '$$local_PlantaFixaVozGtar_CodigoGrupo'] }] } } }],
'as' : 'PlantaFixaVozGtar' } },
{ '$unwind' : '$PlantaFixaVozGtar' },
{ '$lookup' : { 'from' : 'CarFixa', 'let' : { 'local_CarFixa_CarFixa_Nrc' : '$Nrc' },
'pipeline' : [{ '$match' : { '$expr' : { '$and' : [{ '$eq' : ['$PlantaFixaVozGtar.Nrc', '$$local_CarFixa_CarFixa_Nrc'] }] } } }],
'as' : 'CarFixa' } }, { '$unwind' : '$CarFixa' },
{ '$project': { 'CodigoGrupo': 1, 'PlantaFixaVozGtar.CodigoGrupo': 1, 'PlantaFixaVozGtar.Nrc': 1, 'CarFixa.Nrc': 1, 'ArrecadacaoFixa._id': 1 } },
{ '$skip' : 0 }, { '$limit' : 10 }])
1 - First lookup returns only records where they found the math between them.
2 - Already in the second lookup it is not doing any joining between the collections and returning information that does not intersect.
Has anyone ever been through this?