Join two tables in mongo even if there is no entry in the right table

Working with Data
1

Hi All,
Please help me out by letting me know how to write the syntax for joining two tables in mongodb. I want all the entries in the left table. If there is no matching entry in the right table then it should be populated as blank field.

2

In mongo parlance, it is collections rather than tables, it is documents rather than entries. The operation is $lookup rather than join.

If you want more details you will have to provide more details by sharing samble documents from both collections. Sample resulting documents are also needed for all use cases. Also dhare what you have tried and explain how it fails to produce the desired output.

3

Hi Steeve,
Thanks for replying. I wrote the following statements.

[{"$project":{“column names”}},
{ “$lookup”:{
“from”:“sample1”,
“localField”: “xyz”,
“foreignField”: “abc”,
“as”:“sample” }
}]

So lets take previously I had 100 entries with xyz as my parent column and sample1 had 50 entries with abc as the foreign column. Now when I am applying $lookup, I am getting only 50 entries which are there in both the tables but I want all the 100 entries where for the 50 matching documents in main collection, documents from sample1 will come and populate in the output section and for the remaining 50 documents it should populate blank documents.

for example:
Main collection is like as following:
xyz
1
2
3
4
5

sample 1:
abc
1
2
3

After $lookup I am getting following result:
xyz abc
1 1
2 2
3 3

but I want following:
xyz abc
1 1
2 2
3 3
4
5

I hope I have made my doubt clear. Let me know if you need more information for clearing my doubt.

4

please post real sample documents and real results from running the code you shared. the result you posted are not consistent with the code. ee need real json documents, not tabular data that cannot be used with editing.

5

Hi Steeve,
[{$project:{“orderRetrieveId”:1}},
{"$lookup":{
“from”: “sample2”,
“localField”:“orderRetrieveId”,
“foreignField”:“orderRetrieveId”,
“as”: “mainsample”}
},
{"$unwind":"$rawfile"},
{"$project":{“orderRetrieveID”:1,“rawfile.orderRetrieveId”:1}}
]

PFA sample documents.

Now in my first file there are 47 entries and in 2nd one there are 46 entries, now after $lookup I am getting 46 entries only which are matching in both files. But i want all the 47 entries with the 2nd column entry as empty for the non-matching entry.

6

I am not allowed to upload the documents.

7

Why are you doing the following?

There is no field rawfile. After the $project you only have orderRetrieveId and _id. After the $lookup you now have the fields _id, orderRetreveId and mainsample. That is where you should stop your aggregation.

What do you mean by that? You really cannot cut-n-paste real documents?

8

Hi Steeve,

{"$unwind":"$rawfile"}

I mistyped it, in actual I have written {"$unwind":"$mainsample"}

“There is no field rawfile . After the $project you only have orderRetrieveId and _id. After the $lookup you now have the fields _id, orderRetreveId and mainsample. That is where you should stop your aggregation.”

Okay I will try this.

Thanks for your help.

9

Hi @Abhishek_Jain3 , I have the same issue as you were, have you found any solution?