[SERVER-21469] $lookup is not working in case of the localField is array Created: 16/Nov/15 Updated: 08/Oct/19 Resolved: 20/Sep/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 3.2.0-rc2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Mohamed Hawwas | Assignee: | Charlie Swanson |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Windows7 64 |
||
| Issue Links: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | Windows | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
When use $lookup to refer an array of keys to other collection. it give unexpected result. |
| Comments |
| Comment by David Storch [ 08/Oct/19 ] | |||||||||||||||||||||||||||||||||||||
|
Please note that this ticket was resolved as a duplicate of Best, | |||||||||||||||||||||||||||||||||||||
| Comment by Adam Rackis [ 07/Oct/19 ] | |||||||||||||||||||||||||||||||||||||
|
Hi - is there any update on this issue? Are there plans to reverse this behavior in future versions of Mongo? Version 4.0.4 shows the same behavior.
The current workarounds of using $unwind followed by $lookup, followed by $group are really, really messy, and unless I'm missing something, fails for a lot of use cases. Ie, when $grouping to reverse the $unwind, you also need to somehow combine each of the unwinded item's join results, which is impossible to do without messing up the join's sorting results, if there are any. | |||||||||||||||||||||||||||||||||||||
| Comment by Charlie Swanson [ 20/Sep/16 ] | |||||||||||||||||||||||||||||||||||||
|
Hi LordFoobar, I apologize that the solution above did not work. I've a re-worked this ticket to show that it is a duplicate of the linked In the meantime, a workaround to your trouble with re-grouping would be to rename the field to no longer contain a dot, by adding a $project stage in between the $unwind and the $group. I apologize for the inconvenience, and I hope | |||||||||||||||||||||||||||||||||||||
| Comment by Yanick Rochon [X] [ 20/Sep/16 ] | |||||||||||||||||||||||||||||||||||||
|
This does not resolve for me and the proposed solution is nothing but awkward and redundant. (see http://stackoverflow.com/questions/39581032/mongodbs-aggregation-from-nested-key-returns-nothing) Can someone explain why $lookup cannot just collect the field values as it does when performing a normal search, and use $in instead of equality to generate the aggregated field? This would prevent complex $unwind and redundant $group ing. | |||||||||||||||||||||||||||||||||||||
| Comment by Kelsey Schubert [ 17/Nov/15 ] | |||||||||||||||||||||||||||||||||||||
|
Hi mahawas, $unwind can undone using $group. Here is a quick example:
Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related questions please post on the mongodb-users group or Stack Overflow with the mongodb tag. Kind regards, | |||||||||||||||||||||||||||||||||||||
| Comment by Mohamed Hawwas [ 17/Nov/15 ] | |||||||||||||||||||||||||||||||||||||
|
Thank you Thomas Schubert and Charlie Swanson for your replies, But I think using unwind will cause performance issue if the collection contain big data. And how I can reverse $unwind? Regards, | |||||||||||||||||||||||||||||||||||||
| Comment by Kelsey Schubert [ 16/Nov/15 ] | |||||||||||||||||||||||||||||||||||||
|
Hi mahawas, This behavior you're seeing is expected. Please see Charlie's comment above for clarification. I've opened Kind regards, | |||||||||||||||||||||||||||||||||||||
| Comment by Charlie Swanson [ 16/Nov/15 ] | |||||||||||||||||||||||||||||||||||||
|
Hi anonymous.user, I think you'll just need an {$unwind: '$item'} stage before the $lookup, and then it will have a more expected behavior. The $lookup stage is trying to do an equality match between the item.ID field of the documents in orders, but the field item is an array, so it does not have a field named ID. The result is that the $lookup stage treats item.ID as null, and so looks for an equality match on null in the inventory collection. If you put an $unwind before the $lookup, the incoming documents will look like this instead:
And then item.ID will be the value you intended, and can match the documents in the inventory collection. If you wanted the order with an _id of 3 to be included, you should include the preserveNullAndEmptyArrays option to the $unwind stage. If you have any suggestions for the documentation on how to make this more clear, I'd welcome any feedback! | |||||||||||||||||||||||||||||||||||||
| Comment by Kelsey Schubert [ 16/Nov/15 ] | |||||||||||||||||||||||||||||||||||||
|
Summary: If localField is a key inside of an array (eg "item.ID") $Lookup treats the localField as if it did not exist when identifying matches. I have verified this behavior on 3.2.0-rc2 with WiredTiger.
https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup |