[SERVER-22384] Add $lookUpOne to retrieve single Object on result Created: 30/Jan/16 Updated: 06/Apr/23 |
|
| Status: | Open |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 3.2.1 |
| Fix Version/s: | features we're not sure of |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Jose Antonio Illescas Olmo | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 5 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||
| Description |
|
Now $lookup returns array of matched Objects, this new return single Object (as single Object field, not Array) |
| Comments |
| Comment by Asya Kamsky [ 22/Apr/19 ] | ||||||||||||||||||||||||||||||||||||
|
roman_lehnert expressive $lookup (which allows specifying the full pipeline) allows you to add {$limit:1} to the $lookup directly.
| ||||||||||||||||||||||||||||||||||||
| Comment by Roman Lehnert [ 16/Apr/19 ] | ||||||||||||||||||||||||||||||||||||
|
If i do not misunderstand sth. there might be one more reason for introducing a $lookupOne: The server does only know the number of resulting docs of a $lookup, followed by $unwind after the execution. And thus, the optiminzer cannot change a [$lookup, $unwind, $limit] to [$limit, $lookup, $unwind]. But a [($lookup(limit: 1)), $unwind, $limit] could be optimized to [$limit, $unwind, $lookup(limit: 1)] if the optimizer can inspect the stage details. If the optimizer cannot inspect the stage details, but only the stagenames, then the followin can be optimized: [$lookupOne, $limit] to [$limit, $lookupOne]
| ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 01/Mar/18 ] | ||||||||||||||||||||||||||||||||||||
|
This was a bug in the original 3.6.0 release. The fix has been backported to 3.6.3 via BACKPORT-1528 so now that it's available you should be able to use expressive lookup with $match and $limit (and $unwind) which will be as fast as anything $lookupOne would do. | ||||||||||||||||||||||||||||||||||||
| Comment by Ilya Mokin [ 28/Feb/18 ] | ||||||||||||||||||||||||||||||||||||
|
Asya Kamsky, So the $lookUpOne will be really useful feature. | ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 28/Feb/18 ] | ||||||||||||||||||||||||||||||||||||
|
As of 3.6, more expressive $lookup allows putting {$limit:1} into the from pipeline to limit $lookup to matching only one value, including {$unwind:"$as"} immediately after $lookup will perform equivalent of lookup one. | ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 04/Dec/17 ] | ||||||||||||||||||||||||||||||||||||
|
Sounds like you just need to use $unwind option "preserveNullAndEmptyArrays" https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/index.html#pipe._S_unwind | ||||||||||||||||||||||||||||||||||||
| Comment by Manuel Boira [ 03/Dec/17 ] | ||||||||||||||||||||||||||||||||||||
|
I'm trying to create a view with lookup using $unwind to retrieve 1 object per relationship instead of an array. It works but the resultset is omitting objects which do not have a match. | ||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 25/Jun/17 ] | ||||||||||||||||||||||||||||||||||||
|
Or… yet another implicit behaviour could totally be a solution. :| | ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 25/Jun/17 ] | ||||||||||||||||||||||||||||||||||||
Note that when $lookup is immediately followed by $unwind in the pipeline, the execution of the pipeline will fold this into a single stage:
| ||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 13/Jun/16 ] | ||||||||||||||||||||||||||||||||||||
|
Why add an entirely new pipeline stage instead of a flag to the existing one? | ||||||||||||||||||||||||||||||||||||
| Comment by Jose Antonio Illescas Olmo [ 08/Feb/16 ] | ||||||||||||||||||||||||||||||||||||
|
No, I see $lookupOne as a sortcut (or single step) of $lookup + $unwind | ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 08/Feb/16 ] | ||||||||||||||||||||||||||||||||||||
|
You can use $lookup already to return one object - just add $unwind which will get folded into $lookup and you have just one object. Is there another reason for having another stage in addition to $lookup? | ||||||||||||||||||||||||||||||||||||
| Comment by Jose Antonio Illescas Olmo [ 06/Feb/16 ] | ||||||||||||||||||||||||||||||||||||
|
On relation exists 2 types of relation: to-many, to-one | ||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 01/Feb/16 ] | ||||||||||||||||||||||||||||||||||||
|
joseaio could you explain please what the primary goal of this feature would be? It seems rather limiting to retrieve a single object without being able to specify which one (via $sort, $max, etc). | ||||||||||||||||||||||||||||||||||||
| Comment by Stennie Steneker (Inactive) [ 01/Feb/16 ] | ||||||||||||||||||||||||||||||||||||
|
Hi joseaio, Thank you for the feature suggestion. I've marked this for consideration during the next planning round. Regards, |