Equi join in MongoDB

Working with Data
1

How to do equi join in MongoDB, $lookup is giving left join result

1 Like
2

Hi @vinodkumar_Mallikarj,

$lookup is the equivalent of the SQL join which is a left outer join.
Can you please elaborate your question so we can find a solution?

Can you share maybe a few documents (just the important fields) from your 2 collections and the output documents you are expecting so we can try to write this aggregation pipeline for you or at least give you some directions?

Cheers,
Maxime.

3

Check this out,looks like solution,if you want more complicated join condition,see $lookup with
pipeline maybe.

4 
db.Issues.aggregate(
    [
        {
            $match: {
                "issue_type": "CRASH",
                "zipfile.zip_file_dt_tm": {$gte: "2015-01-01", $lte : "2020-01-01"} ,
                "client.client_mnemonic": { $in: ["AL", "CEC"] },
                "client.domain_name": { $in: ["P3", "P94"] },
                "fin.program_name" : "wmse.exe"// Application column
            }
        },
        {
            $lookup: {
                from: "TEAM",
                localField: "Issues.fin.program_name",
                foreignField: "TEAM.COMPONENT",
                as: "teams"
            }
        },
      {
            $match: {
                "teams":{$ne:[]},
                "teams.ORGANIZATION": "Recle",
                "teams.DEPARTMENT": "Dev",
                "teams.TEAM_NAME": "Sing",
                "teams.BUSINESS_UNIT": "SDev"
            }
        },
        {
            $group: {
                _id: "$zipfile.zip_file_dt_tm",
                Fincnt: { $addToSet: "$fin.fingerprint_id" },
				clientcnt: { $addToSet: "$client.client_mnemonic" }
            }
        },
        {
            $project: {
                F_Count: { $size: "$Fincnt" },
                C_cnt: { $size: "$clientcnt" }
            }
        },
        {
            $sort: {_id :1}
        }
        
    ]
)

I have used the condition "teams":{$ne:[]}, still the query is returning the records.

Actually the query should not return any records as the teams collection does not contain any records with the COMPONENT column value “wmse.exe”