Filtering aggregation after invoking $lookup

Question

Filtering aggregation after invoking $lookup

Is there a way to add a filter after performing an $lookup, or is there another method to achieve this?

The data collection test looks like this:

{ "_id" : ObjectId("570557d4094a4514fc1291d6"), "id" : 100, "value" : "0", "contain" : [ ] }
{ "_id" : ObjectId("570557d4094a4514fc1291d7"), "id" : 110, "value" : "1", "contain" : [ 100 ] }
{ "_id" : ObjectId("570557d4094a4514fc1291d8"), "id" : 120, "value" : "1", "contain" : [ 100 ] }
{ "_id" : ObjectId("570557d4094a4514fc1291d9"), "id" : 121, "value" : "2", "contain" : [ 100, 120 ] }

To select id 100 and aggregate the children:

db.test.aggregate([ {
  $match : {
    id: 100
  }
}, {
  $lookup : {
    from : "test",
    localField : "id",
    foreignField : "contain",
    as : "childs"
  }
}]);

After executing the query, we get:

{  
  "_id":ObjectId("570557d4094a4514fc1291d6"),
  "id":100,
  "value":"0",
  "contain":[ ],
  "childs":[ {  
      "_id":ObjectId("570557d4094a4514fc1291d7"),
      "id":110,
      "value":"1",
      "contain":[ 100 ]
    },
    {  
      "_id":ObjectId("570557d4094a4514fc1291d8"),
      "id":120,
      "value":"1",
      "contain":[ 100 ]
    },
    {  
      "_id":ObjectId("570557d4094a4514fc1291d9"),
      "id":121,
      "value":"2",
      "contain":[ 100, 120 ]
    }
  ]
}

However, if we only want children that have a matching value of "1," the expected result should be:

{  
  "_id":ObjectId("570557d4094a4514fc1291d6"),
  "id":100,
  "value":"0",
  "contain":[ ],
  "childs":[ {  
      "_id":ObjectId("570557d4094a4514fc1291d7"),
      "id":110,
      "value":"1",
      "contain":[ 100 ]
    },
    {  
      "_id":ObjectId("570557d4094a4514fc1291d8"),
      "id":120,
      "value":"1",
      "contain":[ 100 ]
    }
  ]
}

javascript mongodb mongodb-query aggregation-framework

Answer 1

Answer №1

Discussing a topic unrelated to the use of $lookup, this answer provides techniques for filtering after utilizing $lookup. Two methods are presented:

MongoDB 3.6 - Sub-pipeline

db.test.aggregate([
    { "$match": { "id": 100 } },
    { "$lookup": {
      "from": "test",
      "let": { "id": "$id" },
      "pipeline": [
        { "$match": {
          "value": "1",
          "$expr": { "$in": [ "$$id", "$contain" ] }
        }}
      ],
      "as": "childs"
    }}
])

Earlier - $lookup + $unwind + $match coalescence

db.test.aggregate([
    { "$match": { "id": 100 } },
    { "$lookup": {
        "from": "test",
        "localField": "id",
        "foreignField": "contain",
        "as": "childs"
    }},
    { "$unwind": "$childs" },
    { "$match": { "childs.value": "1" } },
    { "$group": {
        "_id": "$_id",
        "id": { "$first": "$id" },
        "value": { "$first": "$value" },
        "contain": { "$first": "$contain" },
        "childs": { "$push": "$childs" }
     }}
])

If you question why would you $unwind as opposed to using $filter on the array, then read Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size for all the detail on why this is generally necessary and far more optimal.

For releases of MongoDB 3.6 and onwards, then the more expressive "sub-pipeline" is generally what you want to "filter" the results of the foreign collection before anything gets returned into the array at all.

The underlying theme here is that effective data manipulation practices can be achieved without needing complex aggregation operations such as joins or sub-queries, showcasing the efficiency of retrieving related items directly instead.

Original

This response emphasizes that using $lookup may not be the most efficient approach for achieving desired outcomes. It suggests simpler alternatives like $filter or $redact for filtering results post $lookup.

The examples provided illustrate different ways of achieving the same outcome, underscoring the importance of optimizing query performance based on specific requirements.

A key takeaway is the flexibility offered by MongoDB in tailoring queries to efficiently retrieve desired data without relying heavily on advanced aggregation features.

Answer 2

Discussing a topic unrelated to the use of $lookup, this answer provides techniques for filtering after utilizing $lookup. Two methods are presented:

MongoDB 3.6 - Sub-pipeline

db.test.aggregate([
    { "$match": { "id": 100 } },
    { "$lookup": {
      "from": "test",
      "let": { "id": "$id" },
      "pipeline": [
        { "$match": {
          "value": "1",
          "$expr": { "$in": [ "$$id", "$contain" ] }
        }}
      ],
      "as": "childs"
    }}
])

Earlier - $lookup + $unwind + $match coalescence

db.test.aggregate([
    { "$match": { "id": 100 } },
    { "$lookup": {
        "from": "test",
        "localField": "id",
        "foreignField": "contain",
        "as": "childs"
    }},
    { "$unwind": "$childs" },
    { "$match": { "childs.value": "1" } },
    { "$group": {
        "_id": "$_id",
        "id": { "$first": "$id" },
        "value": { "$first": "$value" },
        "contain": { "$first": "$contain" },
        "childs": { "$push": "$childs" }
     }}
])

If you question why would you $unwind as opposed to using $filter on the array, then read Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size for all the detail on why this is generally necessary and far more optimal.

For releases of MongoDB 3.6 and onwards, then the more expressive "sub-pipeline" is generally what you want to "filter" the results of the foreign collection before anything gets returned into the array at all.

The underlying theme here is that effective data manipulation practices can be achieved without needing complex aggregation operations such as joins or sub-queries, showcasing the efficiency of retrieving related items directly instead.

Original

This response emphasizes that using $lookup may not be the most efficient approach for achieving desired outcomes. It suggests simpler alternatives like $filter or $redact for filtering results post $lookup.

The examples provided illustrate different ways of achieving the same outcome, underscoring the importance of optimizing query performance based on specific requirements.

A key takeaway is the flexibility offered by MongoDB in tailoring queries to efficiently retrieve desired data without relying heavily on advanced aggregation features.

Filtering aggregation after invoking $lookup

Answer №1

MongoDB 3.6 - Sub-pipeline

Earlier - $lookup + $unwind + $match coalescence

Original

Similar questions

Is there a way to adjust the position of a Bootstrap 5 dropdown menu in real-time using javascript or jQuery?

Navigating in a Curved Path using Webkit Transition

Troubleshooting problems with displaying views due to asynchronous $http.get calls in AngularJS

How to keep your vue-router up-to-date

In order to access the localStorage from a different component, a page refresh is required as it is

ReactJs - Reactstrap | Issue with Jumbotron displaying background color

Using Selenium in conjunction with browsermob-proxy to generate new HAR files for redirected web pages

Pair each element with an array of objects and add them to a fresh array

Fetch data dynamically with jQuery AJAX

Creative ways to use images as borders with CSS in React JS

The performance of Ionic scroll is hindered by a slow response time coupled with a

Unable to input characters consecutively into the text field because it is being displayed as a distinct function within the component

An array containing concatenated values should be transferred to the children of the corresponding value

What steps can I take to ensure that Mongo 3.0 / WiredTiger loads my entire database into memory?

To access a restricted selection of images stored in Firebase

Guide to adding and showing records without the need to refresh the webpage using CodeIgniter

Checkbox fails to display as checked

Unable to make changes while state transition is in progress

Adding a local image to Firebase Storage in Angular5 / Ionic3

I'm having trouble grasping the sequence of events in this async example