Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-25051

$lookup should support an "restrictWithMatch" option

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Aggregation Framework
    • Labels:
      None
    • Query Optimization

      In many use cases, a user will want to restrict the documents $lookup searches via some additional parameter. For example, they may want:

      {
        $lookup: {
          from: 'employees',
          localField: '_id',
          foreignField: 'manager',
          as: 'manages'
        }
      }
      

      But, they may only want to look for employees that have "active: true". Currently, they can do this in two ways:

      • Follow the $lookup with a $unwind on 'manages', and then a $match.
      • Follow the $lookup with a $project and a $filter on 'manages'.
        We will optimize the former case (SERVER-21612) and execute the $match as a query on the foreign collection, but this is not immediately clear from the pipeline.

      Another option is, in 3.4, the user could use $graphLookup with a 'maxDepth' of 0 and a 'restrictSearchWithMatch' parameter to achieve the same effect--a poor workaround.

      As such, $lookup should allow the user to specify an additional option, 'restrictWithMatch', that is a $match-type filter that is applied to any document from the foreign collection to be returned.

      Other features available for $graphLookup that are missing for $lookup:

      • localField is a field path only, startWith is an arbitrary expression.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            benjamin.murphy Benjamin Murphy
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated: