mongodb aggregation pipeline

Q. How to lookup and filter in a pipeline?

To perform a lookup and filter in a MongoDB aggregation pipeline, follow these steps:

1. Use $lookup for Joining Collections

The $lookup stage allows you to join another collection based on a foreign key.

{
  "$lookup": {
    "from": "academymodels",        // The collection to join
    "localField": "academy",        // The field in the current collection
    "foreignField": "_id",          // The field in the "from" collection
    "as": "academyDetails"          // The alias for the joined documents
  }
}

2. Use $match to Filter the Results

Once you've performed the $lookup, you can filter the documents based on conditions.

{
  "$match": {
    "businessDays": { "$size": 0 }  // Filtering condition
  }
}

3. Combine $lookup and $match in a Pipeline

A complete pipeline combining $lookup and $match would look like this:

[
  {
    "$lookup": {
      "from": "academymodels",
      "localField": "academy",
      "foreignField": "_id",
      "as": "academyDetails"
    }
  },
  {
    "$match": {
      "businessDays": { "$size": 0 }  // Filters documents where businessDays is empty
    }
  }
]

Explanation

1. $lookup joins academymodels with courtmodes using the academy field.

2. $match filters out documents where businessDays is empty.

Next Steps

• If you're not getting results, check if academy values in courtmodes match _id values in academymodels.

• Ensure businessDays exists and has array data.

• Use $project after $lookup to control which fields appear in the final output.

Q. Why lookup result is array? It is only one to one relationship.

The $lookup stage in MongoDB always returns an array because MongoDB is designed to support one-to-many relationships by default. Even in a one-to-one relationship, $lookup treats it as a potential one-to-many and returns an array of matched documents.

Why Does $lookup Return an Array?

• MongoDB does not enforce one-to-one relationships at the database level.

• Even if there's only one matching document, $lookup still wraps the result in an array.

• This ensures consistent behavior across both one-to-one and one-to-many relationships.

How to Convert the $lookup Array into a Single Object?

If you expect only one matching document, you can use $unwind to transform the array into an object.

Modify the Pipeline

[
  {
    "$lookup": {
      "from": "academymodels",
      "localField": "academy",
      "foreignField": "_id",
      "as": "academyDetails"
    }
  },
  {
    "$unwind": {
	  "path": '$ticketPlanDetails',
	  "preserveNullAndEmptyArrays": true
	}  // Converts the array to a single object
  }
]

How $unwind Works

• If academyDetails contains one document, it will replace the array with that document.

• If no match is found, the field will be empty (you can handle it with preserveNullAndEmptyArrays: true).

Alternative: Using $arrayElemAt

If you don't want to unwind but still need the first element as an object:

[
  {
    "$lookup": {
      "from": "academymodels",
      "localField": "academy",
      "foreignField": "_id",
      "as": "academyDetails"
    }
  },
  {
    "$set": {
      "academyDetails": { "$arrayElemAt": ["$academyDetails", 0] }
    }
  }
]

Why $arrayElemAt?

• It extracts only the first element from the array.

• It keeps documents intact (no duplication, unlike $unwind).

Which One Should You Use?

Let me know if you need further refinements! 🚀