[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:

db.collection.find({
 firstname: 'Jones',
 lastname: 'Sally',
 addresses: {$arrayFind: {
   state:'TX',
   $sort:{zip:1},
 }}
)

 

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. 

Generated at Thu Feb 08 04:39:14 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.