OPERATIONAL DEFECT DATABASE
...
...
When use $lookup to refer an array of keys to other collection. it give unexpected result.
david.storch commented on Tue, 8 Oct 2019 18:51:11 +0000: Hi adamrackis@hotmail.com, Please note that this ticket was resolved as a duplicate of SERVER-22881, and that SERVER-22881 is fixed in all versions >=3.3.4. Also, our process is not to re-open tickets that have been shipped in development or production-ready releases, but rather to file a new ticket. Please feel free to file a new ticket describing the behavior you're observing against version 4.0.4 and the team will take a look! We do not currently have plans to revert the behavior added under SERVER-22881, but if you provide details about your problem we can suggest fixes/workarounds either on your end or ours. It may also be helpful to ask for help with our community by posting on the mongodb-user group or on Stack Overflow with the mongodb tag. Best, Dave adamrackis@hotmail.com commented on Mon, 7 Oct 2019 08:34:19 +0000: 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. charlie.swanson commented on Tue, 20 Sep 2016 15:38:04 +0000: 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 SERVER-22881. We have resolved this issue in the upcoming release, but cannot backport this to the 3.2 branch as it is a potentially backwards-breaking behavior change. 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 SERVER-22881 resolves your issue. lordfoobar commented on Tue, 20 Sep 2016 13:53:27 +0000: 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. thomas.schubert commented on Tue, 17 Nov 2015 07:44:24 +0000: Hi mahawas, $unwind can undone using $group. Here is a quick example: db.foo.insert({arr: [{a : 'x'},{a : 'y'},{a : 'z'}]}) db.foo.insert({arr: [{b : 1},{b : 2},{b : 3}]}) db.foo.aggregate([{$unwind : '$arr'}, {$group : {_id: '$_id', arr : {$push : '$arr'}}}]) 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, Thomas mahawas commented on Tue, 17 Nov 2015 05:15:23 +0000: 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, Mohamed. thomas.schubert commented on Mon, 16 Nov 2015 18:59:03 +0000: Hi mahawas, This behavior you're seeing is expected. Please see Charlie's comment above for clarification. I've opened DOCS-6605 to improve the documentation relating to the use arrays in the localField. Kind regards, Thomas charlie.swanson commented on Mon, 16 Nov 2015 16:44:04 +0000: 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: > db.orders.aggregate([{$unwind: '$item'} ]); { "_id" : 1, "item" : { "ID" : "abc" }, "price" : 12, "quantity" : 2 } { "_id" : 1, "item" : { "ID" : "jkl" }, "price" : 12, "quantity" : 2 } { "_id" : 2, "item" : { "ID" : "jkl" }, "price" : 20, "quantity" : 1 } 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! thomas.schubert commented on Mon, 16 Nov 2015 09:11:42 +0000: 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. // Reproduction Steps db.inventory.insert({ "_id" : 1, "sku" : "abc", "description" : "product 1", "instock" : 120 }); db.inventory.insert({ "_id" : 2, "sku" : "def", description: "product 2", "instock" : 80 }); db.inventory.insert({ "_id" : 3, "sku" : "jkl", "description" : "product 3", "instock" : 60 }); db.inventory.insert({ "_id" : 5, "sku" : null, "description" : "Incomplete" }); db.inventory.insert({ "_id" : 6}); db.orders.insert({"_id" : 1, "item" : [ { "ID" : "abc" } , { "ID" : "jkl" } ], "price" : 12, "quantity" : 2 }); db.orders.insert({ "_id" : 2, "item" : [ { "ID" : "jkl" } ], "price" : 20, "quantity" : 1 }); db.orders.insert({ "_id" : 3}); db.orders.aggregate([ { $lookup: { from: "inventory", localField: "item.ID", foreignField: "sku", as: "inventory_docs" } } ]); // Output // { "_id" : 1, "item" : [ { "ID" : "abc" }, { "ID" : "jkl" } ], "price" : 12, "quantity" : 2, "inventory_docs" : [ // { "_id" : 5, "sku" : null, "description" : "Incomplete" }, // { "_id" : 6 } // ] } // { "_id" : 2, "item" : [ { "ID" : "jkl" } ], "price" : 20, "quantity" : 1, "inventory_docs" : [ // { "_id" : 5, "sku" : null, "description" : "Incomplete" }, // { "_id" : 6 } // ] } // { "_id" : 3, "inventory_docs" : [ // { "_id" : 5, "sku" : null, "description" : "Incomplete" }, // { "_id" : 6 } // ] } // Expected Output based on documentation // { "_id" : 1, "item" : [ { "ID" : "abc" }, { "ID" : "jkl" } ], "price" : 12, "quantity" : 2, "inventory_docs" : [ // { "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }, // { "_id" : 4, "sku" : "jkl", "description" : "product 4", "instock" : 70 } // ] } // { "_id" : 2, "item" : [ { "ID" : "jkl" } ], "price" : 20, "quantity" : 1, "inventory_docs" : [ // { "_id" : 4, "sku" : "jkl", "description" : "product 4", "instock" : 70 } // ] } // { "_id" : 3, "inventory_docs" : [ // { "_id" : 5, "sku" : null, "description" : "Incomplete" }, // { "_id" : 6 } // ] } https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup
-----inventory-------- /* 1 */ { "_id" : 1, "sku" : "abc", "description" : "product 1", "instock" : 120 } /* 3 */ { "_id" : 3, "sku" : "ijk", "description" : "product 3", "instock" : 60 } /* 5 */ { "_id" : 5, "sku" : null, "description" : "Incomplete" } /* 6 */ { "_id" : 6 } ----Orders collection---- /* 1 */ { "_id" : 1, "item" : [ { "ID" : "abc" }, { "ID" : "jkl" } ], "price" : 12, "quantity" : 2 } /* 2 */ { "_id" : 2, "item" : [ { "ID" : "jkl" } ], "price" : 20, "quantity" : 1 } /* 3 */ { "_id" : 3 } ----Aggregate function --- db.orders.aggregate([ { $lookup: { from: "inventory", localField: "item.ID", foreignField: "sku", as: "inventory_docs" } } ])
Click on a version to see all relevant bugs
MongoDB Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.
