is this the right way to join two collections and combine results

user_395255 0
Viewed 58

I am trying to join two collections and get fields from both the collections. Collections have very basic structure and this is how they look like:

"products": [
    {
      "_id": 0,
      "name": "product 0",
      "desc": "some product",
      "sales_reps": [
        {
          "sales_rep_id": 0,
          "is_doing_good": true
        },
        {
          "sales_rep_id": 1,
          "is_doing_good": true
        }
      ]
    }
  ]

  "sales_rep_master": [
    {
      "_id": 0,
      "name": "sales rep 0"
    },
    {
      "_id": 1,
      "name": "sales rep 1"
    },
    {
      "_id": 2,
      "name": "sales rep 2"
    }
  ]

I am trying to join them on products.sales_reps.sales_rep_id = sales_rep_master._id. This is how my query looks like:

[
  {
    "$match": { <--Filter on product._id
      "_id": 0 
    }
  },
  {
    "$unwind": { <-- Expand sales reps array
      "path": "$sales_reps" 
    }
  },
  {
    "$lookup": { <-- join with sales rep master and filter our where is_doing_well is false
      "from": "sales_rep_master",
      "let": {
        "sr_id": "$sales_reps.sales_rep_id",
        "is_doing_well": "$sales_reps.is_doing_good"
      },
      "pipeline": [
        {
          "$match": {
            "$and": [
              {
                "$expr": {
                  "$eq": [
                    "$_id",
                    "$$sr_id"
                  ]
                }
              },
              {
                "$expr": {
                  "$eq": [
                    "$$is_doing_well",
                    true
                  ]
                }
              }
            ]
          }
        },
        {
          "$addFields": {
            "doing_good": "$$is_doing_well"
          }
        }
      ],
      "as": "sales_reps"
    }
  },
  {
    "$unwind": { <<- expand newly created sales reps array
      "path": "$sales_reps"
    }
  },
  {
    "$group": { <-- Group product and sales reps
      "_id": "$_id",
      "product": {
        "$first": "$$ROOT"
      },
      "sales_reps": {
        "$push": "$sales_reps"
      }
    },
    
  },
  {
    "$set": { <-- add sales_reps inside product
      "product.sales_reps": "$sales_reps"
    }
  },
  {
    "$replaceRoot": { <-- replace root
      "newRoot": "$product"
    }
  }
]

This query is working fine and I am getting expected output but I feel like I am doing something wrong here as this seems to be doing too much just to get fields from two collections. There are more stages after that which I have excluded.

Here is the demo: https://mongoplayground.net/p/_Tz3fm4a-J8

Am I doing something wrong here or this is how it is supposed to be?

mongodb-query mongodb
3 Answers

You can optimize your query,

  • $match you both conditions, to reduce unmatched documents
  • $unwind deconstruct sales_reps array
  • $match sales_reps.is_doing_good condition to reduce unmatched document
  • $lookup to join collection
  • $unwind deconstruct sales_reps
  • $addFields to add field doing_good
  • $group by id and reconstruct sales_reps array
db.products.aggregate([
  {
    $match: {
      _id: 0,
      "sales_reps.is_doing_good": true
    }
  },
  { $unwind: "$sales_reps" },
  { $match: { "sales_reps.is_doing_good": true } },
  {
    $lookup: {
      from: "sales_rep_master",
      localField: "sales_reps.sales_rep_id",
      foreignField: "_id",
      as: "sales_reps"
    }
  },
  { $unwind: "$sales_reps" },
  { $addFields: { "sales_reps.doing_good": true } },
  {
    $group: {
      _id: "$_id",
      desc: { $first: "$desc" },
      name: { $first: "$name" },
      sales_reps: { $push: "$sales_reps" }
    }
  }
])

Playground

Another possible way, reduce $addFields pipeline from above example,

  • $mergeObjects to merge field in side $push
  {
    $group: {
      _id: "$_id",
      desc: { $first: "$desc" },
      name: { $first: "$name" },
      sales_reps: {
        $push: {
          $mergeObjects: [{ doing_good: true }, "$sales_reps"]
        }
      }
    }
  }

Playground

For the reference follow aggregation-pipeline-optimization, compare query's response stats using explain()

user_89871280

The part that can be simplified, is the $lookup by using localField and foreignField

Then I tied a different approach with a $project instead of the $set and $replaceRoot

mongoplayground

db.products.aggregate([
  {
    "$match": {
      "_id": 0
    }
  },
  {
    "$unwind": {
      "path": "$sales_reps"
    }
  },
  {
    "$lookup": {
      "from": "sales_rep_master",
      "localField": "sales_reps.sales_rep_id",
      "foreignField": "_id",
      "as": "sales_rep_master"
    }
  },
  {
    "$unwind": {
      "path": "$sales_rep_master"
    }
  },
  {
    "$project": {
      "_id": 1,
      "desc": 1,
      "name": 1,
      "sales_rep_master": 1,
      "sales_reps": {
        "_id": "$sales_rep_master._id",
        "name": "$sales_rep_master.name",
        "doing_good": "$sales_reps.is_doing_good",
        
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "desc": {
        "$first": "$desc"
      },
      "name": {
        "$first": "$name"
      },
      "sales_reps": {
        "$addToSet": "$sales_reps"
      }
    }
  }
])
user_126417170

There are few thing I consider. You don't need to use $expr for each condition. If you going to use $in to compare, then you don't need to $unwind. $unwind cost always when we increase data. Ref JIRA ticket. But considering data size, you can use $in or $unwind.

db.products.aggregate([
  {
    "$match": {
      "_id": 0
    }
  },
  {
    $addFields: {
      sales_reps: {
        $filter: {
          input: "$sales_reps",
          cond: {
            $eq: [ "$$this.is_doing_good", true]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "sales_rep_master",
      "let": { "sr_id": "$sales_reps.sales_rep_id" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                { "$in": [ "$_id","$$sr_id"] },
                // { other condition }
              ]
            }
          }
        },
        {
          "$addFields": {
            "doing_good": true
          }
        }
      ],
      "as": "sales_reps"
    }
  }
])

Working Mongo playground

user_79757710
Related