[SERVER-85612] $lookup on array field matches documents without the foreignField Created: 23/Jan/24  Updated: 23/Jan/24

Status: Needs Verification
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Clark McCauley Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

https://mongoplayground.net/p/ZBs-X9VnNsX.

Participants:

 Description   

I've created a MongoDB playground the illustrates the issue: https://mongoplayground.net/p/ZBs-X9VnNsX.

 

If this is not a bug, it's certainly not the behavior I expected. It appears that when I do two sequential lookups, where the second lookup uses an ID from the document produced by the first lookup, if the any documents in the second lookup's collection are missing the foreignField, then they will be returned in the $lookup.

 

I suspect that the cause is because if the $lookup.fromField is an array, and that array is empty, we match $foriegnField on `null`. Is this actually preferred behavior?



 Comments   
Comment by Clark McCauley [ 23/Jan/24 ]

Workaround is to check that the foreign field is not null in the $lookup pipeline. https://mongoplayground.net/p/lKh2V59l6_v

Comment by Clark McCauley [ 23/Jan/24 ]

Updated playground with a comment and example showing that introducing a matching document in the first $lookup ensures that the second $lookup doesn't match on `null`. https://mongoplayground.net/p/SG52GL6sgOo

Generated at Thu Feb 08 06:58:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.