[SERVER-22497] Allow from field of $lookup and $graphLookup to be an expression Created: 05/Feb/16  Updated: 06/Apr/23

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Faleij Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 25
Labels: stage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-27314 Only validate "from" pipeline structu... Closed
is related to SERVER-58411 Deep find nested objects in return do... Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Participants:

 Description   

Example Query

{
    $lookup: {
        from: "$foo.collectionName",
        localField: "foo.ref",
        foreignField: "bar",
        as: "foo"
    }
}



 Comments   
Comment by Prakash Chokalingam [ 02/Oct/21 ]

Just revoted. If this issue is resolved it would be great. It will help us to resolve a lot of dynamic interlinking use cases. Thanks #justchecking

Comment by Katya Kamenieva [ 14/Jun/21 ]

Thanks to everyone for voting and giving your input on this request. Currently, there are no plans for supporting variable `from' collection per document, that would require significant changes in the security and query optimization architectures.

Comment by Oleksii Ignatiuk [ 28/Aug/20 ]

@Benny Ng  It does not seem to be a proper solution since you have to re-code every such lookup by hand.

Comment by Benny Ng [ 06/Feb/20 ]

i still think it is a nice feature to have although we can achieve with below method

{
  $addFields: {
    items: '$$REMOVE',
    orderItems: { $cond: [{ $eq: ['$sys', 'Order'] }, '$items', []] },
    skuItems: { $cond: [{ $eq: ['$sys', 'Sku'] }, '$items', []] },
  },
},
{
  $lookup: {
    from: 'orders',
    let: { items: '$orderItems' },
    pipeline: [
      { $match: { $expr: { $in: ['$_id', '$$orderItems'] } } },
    ],
    as: 'orderItemsDocs',
  },
},
{
  $lookup: {
    from: 'skus',
    let: { items: '$skuItems' },
    pipeline: [
      { $match: { $expr: { $in: ['$_id', '$$skuItems'] } } },
    ],
    as: 'skuItemsDocs',
  },
}

 

 

Comment by Asya Kamsky [ 17/Jan/18 ]

I think I agree that from collection being an expression is not feasible (due to access control method relying on collection names to determine access permissions) but what about localField/foreignField?

Comment by Asya Kamsky [ 24/Jan/17 ]

Should consider allowing other fields to be expressions at the same time (like "localField", "foreignField", etc).

Comment by Dissatisfied Former User [ 13/Jun/16 ]

+1 on supporting DBRefs; the official way to bundle document + collection references.

Comment by Asya Kamsky [ 19/Feb/16 ]

To be able to perform lookups from multiple collections is the goal of this request.

That's what I wanted to confirm.

So the request is to allow looking up from different collections, and to provide the collection via expression (which could be based on a field in the document itself).

Comment by Scott Hernandez (Inactive) [ 19/Feb/16 ]

It might be good to support DBRefs as well for this so that ODMs can use this to do the lookup at the server rather than on the client.

Comment by Faleij [ 18/Feb/16 ]

"$foo.collectionName" would represent a collection name from the document itself. To be able to perform lookups from multiple collections is the goal of this request.

Comment by Asya Kamsky [ 18/Feb/16 ]

Right now "from" has to be a literal string which represents collection name. Does "$foo.collectionName" represent the same string but derived from some expression? Do you mean that you want it to take the string representing the collection name from the document itself? As opposed to it being a literal string requirement?

Just making sure I understand the request correctly. Note that $lookup is supposed to be from a single particular collection. I'm trying to understand what's underlying the request - is it to be able to $lookup in different collections depending on some value in each document?

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