Add support for $lookup with `let`/`pipeline` form to aggregation [DATAMONGO-2467]

[spring-projects-issues]

Shiv Jha-Mathur opened DATAMONGO-2467 and commented

Perform uncorrelated subqueries between two collections as well as allow other join conditions besides a single equality match:

{
   $lookup:
     {
       from: <collection to join>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to execute on the collection to join> ],
       as: <output array field>
     }
}

---

Reference URL: https://docs.mongodb.com/master/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries

1 votes, 2 watchers

[spring-projects-issues]

Herman Bovens commented

I think this one deserves Major priority instead of Minor.  Uncorellated subqueries and multiple join conditions are just two possible uses, but there is more.  Not being able to use a pipeline for lookup leads to inefficient queries: instead of doing a $project operation inside the $lookup operation when not all fields of the target document are needed, people fall back to doing the projection after the lookup (or even not at all).  Limiting the number of fields has proven to be faster for me, definitely much faster if the index on the target collection that is used contains all of the projected fields

[mohitangiras]

One of the reason to implement this is the error below, this occurs with large data if lookup is not followed by Unwind, the large array that is formed in normal lookup leads to limit of 16MB, where as Mongo engine restructures lookup operation if its a let/pipeline combination or followed by Unwind operation:

2021-06-24T14:40:51.698+0000 E QUERY [js] Error: command failed: {
"operationTime" : Timestamp(1624545642, 1),
"ok" : 0,
"errmsg" : "Total size of documents in test matching pipeline's $lookup stage exceeds 104857600 bytes",
"code" : 4568,
"codeName" : "Location4568",
"$clusterTime" : {
"clusterTime" : Timestamp(1624545642, 1),
"signature" : {
"hash" : BinData(0,"Zerftfjasd/6gn04z3lD8D6o="),
"keyId" : NumberLong("69195668423432432432")
}
}
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:580:17
assert.commandWorked@src/mongo/shell/assert.js:673:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9

[llyfn]

Any update on this issue?

[LasseMNielsen]

ye this would be preferede instead of writing custom Bson docs. Any update ?

[keyzj]

Any update on this issue?