[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:
Duplicate
is duplicated by SERVER-27589 Return single object instead of array... Closed
is duplicated by SERVER-32417 Add syntax to express $lookup-$unwind... Closed
is duplicated by SERVER-40635 Introduce $lookupOne as an optimzable... Closed
Related
related to SERVER-28705 Add optimization to execute $lookup o... Closed
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,
lookup pipeline + unwind is extremely slow in my case! Maybe because that suggests additional deep clone for the records or something like that.
Even simple lookup more fast because can use indexes from the second collection. If you use lookup pipeline + $match we can forget about indexes.

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.
I want to list all the objects, even the ones that do not have a matched relationship.
Wouldn't be $lookupOne useful for this purpose?

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 ]

I see $lookupOne as a sortcut (or single step) of $lookup + $unwind

Note that when $lookup is immediately followed by $unwind in the pipeline, the execution of the pipeline will fold this into a single stage:

db.baz.aggregate([{$lookup:{from:"foo",as:"bar",localField:"id",foreignField:"_id"}},{$unwind:"$bar"}],{explain:true})
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
 
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "geo.baz",
					"indexFilterSet" : false,
					"parsedQuery" : {
 
					},
					"winningPlan" : {
						"stage" : "EOF"
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$lookup" : {
				"from" : "foo",
				"as" : "bar",
				"localField" : "id",
				"foreignField" : "_id",
				"unwinding" : {
					"preserveNullAndEmptyArrays" : false
				}
			}
		}
	],
	"ok" : 1
}

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
· $lookup return a list of objects (0..N) => Children of Person
· $lookupOne return only one object (0..1) => Parent of Person
Thanks

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,
Stephen

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