[SERVER-71508] Accessing nested array variable in $lookup+$let+$expr seems undefined Created: 20/Nov/22 Updated: 27/Oct/23 Resolved: 09/Aug/23 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | 4.4.7, 6.0.3 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Igor Nem | Assignee: | Backlog - Query Execution |
| Resolution: | Works as Designed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
| Issue Links: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query Execution
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | |||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | Create collection "machine" with data
Create collection device with data
Execute aggregation
Expected result:
Actual result:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
I'm trying to make a join of two colections by multiple fields, one of them is boolean and in case when this field is "false" |
| Comments |
| Comment by Edwin Zhou [ 09/Aug/23 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
After deeper investigation, this behavior is expected and works as designed. The "$lines.devices.type-c.disabled" field resolves to [false], and {$toBool: [false]} resolves to true since it is an array. We can demonstrate this by using $let to show that m_device_enabled is an array containing false
This is defined behavior, and you will need to use $arrayElemAt, more concisely $first, or do an $unwind if there are multiple elements in lines. Kind regards, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Edwin Zhou [ 25/Jul/23 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thank you for your patience while we investigate this issue—investigating problematic queries can be difficult. The recommendations that my colleagues have provided point to the undefined behavior you noted in your summary: $let on an array in $lookup produces an undefined behavior. I think the behavior can be clearly demonstrated if we simplify your example a bit and add a new field with similar paths:
Each aggregation respectively produces
I do agree with my colleagues that using $addFields with $arrayElemAt to reduce the subarray into a subdocument, or $unwind to produce a set of documents before using $lookup is a way to work around this issue. I hope that you consider the solutions posed in the referenced forum post, or that you engage the MongoDB Developer Community Forums if you need additional help. Given that this behavior seems undefined, I'd like to pass this ticket over to the appropriate team to further investigate whether this behavior is intended. Kind regards, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Igor Nem [ 16/Feb/23 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi, are any news or plans on it, please? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Igor Nem [ 16/Dec/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi Eric,
Thank you
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Eric Sedor [ 16/Dec/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Apologies for any confusion and thanks for writing back. What my colleague Yuan is pointing to is the lines array in the machine document. If you use $arrayElemAt (or $unwind if logically necessary) to extract the lines documents from the array prior to $lookup (as described at the referenced forum post) you can work around the problem you are seeing. But as we look more we do think there could be an issue with the $expr clause referencing the "$$m_device_disabled" variable from the $let clause. It does not seem related to $toBool as far as we can tell so far. Have you seen a case where it works for other data types or in other cases? We wonder if something similar to Eric | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Igor Nem [ 15/Dec/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi @Yuan Fang, actually in my query there is no array field therefore the link you posted and your guess are not relevant. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Yuan Fang [ 15/Dec/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thank you for reporting the issue. It looks like this thread on the forum discusses getting an element in an array in the aggregation pipeline. Can you see if it helps? I quickly glanced at the aggregation pipeline you provided, it seems there is an ambiguity in the $let expression getting the "disabled" field in the machine collection. Thus, I think it is the expected behavior when trying to use $let to address an array field. For any further issues like this, we'd like to encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums. If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project. Regards, |