[SERVER-35230] Add ability to subquery on an array with similar syntax as querying documents Created: 25/May/18 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Rod Adams | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Assigned Teams: |
Query Optimization
|
| Participants: |
| Description |
|
Finding the right way to construct a find against elements in an array field can get rather tricky, and the rules become very opaque for those with less experience with MongoDB.
What I suggest is the ability to essentially nest a subquery into your find/$match, which would treat an array as the new collection. The results of the subquery would replace the array for the rest of the parent find's execution (including what is returned as final result). Example:
We already have pieces of this in filter, elemmatch, etc, but this feels like it would be a much cleaner interface overall, and allow for some constructs we're still missing.
|
| Comments |
| Comment by Rod Adams [ 16/Jul/18 ] |
|
asya – So yes, that is basically what I meant. I don't see it as a limiting issue, since what I'm suggesting is that we basically have a recursive filter & project system. The Array Query would be open to having it's own filter and projection steps, and then the outer document would process it's own filter and projection steps, only with the end projection of the Array Query having replaced the contents of the array. If your conditions for filtering array elements consist wholly in the array data, then you're fine. If not, yes, you'll have to keep some of it around. There'll be corner cases, to be sure, but I believe that in the common case, this would be a lot cleaner interface for users. Thus, I don't see where we'd be giving up functionality in this way... but I'm certainly open to reviewing any counterexamples you provide.
I could see optimizing this as moderately tricky, but 90s of semi-amateur attacking of the problem leads me to believe there's room to make it work. |
| Comment by Asya Kamsky [ 11/Jul/18 ] |
|
rod.adams you specifically say "The results of the subquery would replace the array for the rest of the parent find's execution (including what is returned as final result)." Regarding the parenthetical comment - we always treat the query part separate from the projection part (in find and aggregate). Are you suggesting that new syntax would conflate the two? I don't think that would fit into the existing language semantics very easily (and since sometimes you want to query on one thing - like subset of array matching something - and return different thing, we could not ever remove separate specification for both parts. |