use lookup and group different collection mongodb

Question

Hello I have the following collections

 const TransactionSchema = mongoose.Schema({
    schedule: {
        type: mongoose.Schema.ObjectId,
        required: true,
        ref: "Schedule"
    },
    uniqueCode: {
        type: String,
        required: true
    },
    created: {
        type: Date,
        default: Date.now
    },

    status: {
        type: String,
        required: false
    },
})


    const ScheduleSchema = mongoose.Schema({
    start: {
        type: Date,
        required: true,
    },
    end: {
        type: Date,
        required: false,
    },
    location: {
        type: mongoose.Schema.ObjectId,
        required: true,
        ref: "Location"
    },  

})

and I want to return how many times the schedule appear in transaction ( where the status is equal to 'Active') and group it based on its location Id and then lookup the location collection to show the name. For example I have the following data.

transaction

  [
   {
      "_id":"identifier",
      "schedule":identifier1,
      "uniqueCode":"312312312312",
      "created":"Date",
      "status": 'Active'
   },
   {
      "_id":"identifier",
      "schedule":identifier1,
      "uniqueCode":"1213123123",
      "created":"Date",
      "status": "Deleted"
   }
]

schedule

[
   {
      "_id":identifier1,
      "start":"date",
      "end":"date",
      "location": id1
   },
   {
      "_id":identifier2,
      "start":"date",
      "end":"date",
      "location": id2
   }
]

and I want to get the following result and limit the result by 10 and sort it based on its total value:

[
   {
      "locationName":id1 name,
      "total":1
   },
   {
      "locationName":id2 name,
      "total":0
   }
]

thank you. Sorry for my bad english.

Answer 1

A bit complex and long query.

1. $lookup - schedule collection joins with transaction collection by matching:

• _id (schedule) with schedule (transaction)
• status is Active

and return a transactions array.

2. $lookup - schedule collection joins with location collection to return location array.

3. $set - Take the first document in location array so this field would be a document field instead of an array. [This is needed to help further stage]

4. $group - Group by location._id. And need the fields such as location and total.

5. $sort - Sort by total DESC.

6. $limit - Limit to 10 documents to be returned.

7. $project - Decorate the output documents.

db.schedule.aggregate([
  {
    $lookup: {
      from: "transaction",
      let: {
        scheduleId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$schedule",
                    "$$scheduleId"
                  ]
                },
                {
                  $eq: [
                    "$status",
                    "Active"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "transactions"
    }
  },
  {
    $lookup: {
      from: "location",
      localField: "location",
      foreignField: "_id",
      as: "location"
    }
  },
  {
    $set: {
      location: {
        $first: "$location"
      }
    }
  },
  {
    $group: {
      _id: "$location._id",
      location: {
        $first: "$location"
      },
      total: {
        $sum: {
          $size: "$transactions"
        }
      }
    }
  },
  {
    $sort: {
      "total": -1
    }
  },
  {
    $limit: 10
  },
  {
    $project: {
      _id: 0,
      locationName: "$location.name",
      total: 1
    }
  }
])

Sample Mongo Playground