[SERVER-42306] $lookup with an array of sub objects should allow for an option to allow each sub object to find join partners Created: 19/Jul/19 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Mert Hasret | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 4 |
| Labels: | query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
Schema
Lookup Stage
Expected Output
Output
As you can see above, fields inside the "a" gets overwritten by the $lookup operation, and the field called "a" turns into an object after the aggregation (just like in the behavior of the $unwind operator).
|
| Comments |
| Comment by Vladimir N/A [ 12/Jan/21 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
@Asya Kamsky Thank you for the workaround. It works fine for me in case of an array of sub objects. But I can't realize how to construct the similar query for array of sub arrays. I mean the case like:
So, I need to populate a.props.prop. How can I do that?
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 29/Jul/19 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Note that behavior when comparing array and non-array with localField/foreignField is frequently unexpected/confusing. I would recommend we group those in a single project to clarify (and potentially enhance) array behavior in localField/foreignField syntax. Reusing example from
There is no way to join information from "b" into array "a" in collection a in a $lookup - it has to be done as a separate step. Worse, as described in this ticket, specifying "as" field as anything starting with "a" as top level unexpectedly removes existing information.
Instead of "a" staying an array with new "details" field, we get new "a.details" array. The only workaround for this at the moment is to use a different "as" field and then "merge" the original and new arrays.
Note that I assumed that there could be multiple matches (in this case matching on _id means there can't be, but in another case that may not hold). | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 29/Jul/19 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
According to comments in I didn't see that the ticket was ever filed, however, so I think this ticket can be used to track already known limitation as an new functionality. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Danny Hatcher (Inactive) [ 24/Jul/19 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thank you for the report. I've passed this on to our Query team to take a look. |