[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:
Duplicate
duplicates SERVER-22881 lookup between local (multiple)array... Closed
Related
related to DOCS-6605 Enhanced Equality Match Meaning on $l... Closed
Operating System: Windows
Steps To Reproduce:

-----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"
        }    
   }
])

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 ]

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

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 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.

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:

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

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,
Mohamed.

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 DOCS-6605 to improve the documentation relating to the use arrays in the localField.

Kind regards,
Thomas

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:

> 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!

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.

// 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

Generated at Thu Feb 08 03:57:26 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.