db={"items":[{"Name":"One","Identifier":"RA"},{"Name":"Two","Identifier":"RA"},{"Name":"Three","Identifier":"RA"},{"Name":"Four","Identifier":"SA"},{"Name":"Five","Identifier":"SA"},{"Name":"Six","Identifier":"WA"}],"Mapper":[{"Type":"TAA","Identifier":"RA"},{"Type":"TAB","Identifier":"SA"},{"Type":"TAC","Identifier":"TA"},{"Type":"TAD","Identifier":"WA"}],"Parent":[{"ParentName":"MS","Type":"TAA"},{"ParentName":"MS","Type":"TAB"},{"ParentName":"MS","Type":"TAC"},{"ParentName":"MS","Type":"TAD"},{"ParentName":"GP","Type":"TAA"},{"ParentName":"GP","Type":"TAB"},{"ParentName":"GP","Type":"TAC"}]}

db.items.aggregate([{"$lookup":{"from":"Mapper","localField":"Identifier","foreignField":"Identifier","as":"joined"}},{"$unwind":{"path":"$joined"}},{"$project":{"Type":"$joined.Type","Name":1,"Identifier":1}},{"$lookup":{"from":"Parent","let":{"type":"$Type","indentifier":"$Identifier"},"pipeline":[{"$match":{"$expr":{"$and":[{"$eq":["$ParentName","GP"]},{"$eq":["$$type","$Type"]}]}}},{"$set":{"Identifier":"$$indentifier"}}],"as":"joined"}},{"$unwind":{"path":"$joined","preserveNullAndEmptyArrays":true}},{"$project":{"_id":0,"Name":1,"Identifier":"$joined.Identifier","Type":1}}])