Is there a way to make $lookup behave like findOne?

Working with Data
1

I would like to know whether is there a way to perform a lookup findind just one document, and stop the lookup when it’s found.

It’s something similar to findOne() in mongo, or cross apply in some SQL engines.

It’s quite usefull to speed up some executions in some scenarios.

The option $lookup/$limit: 1 wouldn’t be a replace for what I ask for, event this should return a document instead of an array like $lookup does.

Thank you very much.

2

I think that $lookup with a $limit 1 sub-pipeline followed by a stage like

{ “$set” : { single_document : { “$arrayElemAt” : [ “$lookup_result_with_limit_one” , 0 ] } } }

should be close to what you want.

I am pretty sure that a sub-pipeline with a $limit stops the $lookup as soon as the count is reached so the performance should be on par to any single (invented name) $findOne stage.

4

Thank you very much for your answer Steeve.

Indeed the option $lookup/$limit: 1 is missleading, because I want a limit 1 just for the lookup, and not for the whole aggregation pipeline. I don’t know why well, but when I use sub-pipelines in the extended $lookup, the performance is reduced dramatically.

I have faced this situation several times and I have always to do some kind of hack to get the data…

5

To help you further on this you will need to share

1 - some sample documents from all collections involved
2 - the indexes of all collections involved
3 - the complete pipeline that you uses