How to get count by order in mongodb aggregate?

Question

I have two collections name listings and moods.

listings sample:

{
    "_id": ObjectId("5349b4ddd2781d08c09890f3"),
    "name": "Hotel Radisson Blu",
    "moods": [
      ObjectId("507f1f77bcf86cd799439010"),
      ObjectId("507f1f77bcf86cd799439011")
    ]
}

moods sample:

{
    "_id": ObjectId("507f1f77bcf86cd799439011"),
    "name": "Sports"
},
{
    "_id": ObjectId("507f1f77bcf86cd799439010"),
    "name": "Spanish Food"
},
{
    "_id": ObjectId("507f1f77bcf86cd799439009"),
    "name": "Action"
}

I need this record.

{
    "_id": ObjectId("507f1f77bcf86cd799439011"),
    "name": "Sports",
    "count": 1
},
{
    "_id": ObjectId("507f1f77bcf86cd799439010"),
    "name": "Spanish Food",
    "count": 1
},
{
    "_id": ObjectId("507f1f77bcf86cd799439009"),
    "name": "Action",
    "count": 0
}

I need this type of record. I have no idea about aggregate.

Answer 1

You can do it using aggregate(),

• $lookup to join collection listings
• $match pipeline to check moods _id in listings field moods array

db.moods.aggregate([
  {
    "$lookup": {
      "from": "listings",
      "as": "count",
      let: { id: "$_id" },
      pipeline: [
        {
          "$match": {
            "$expr": { "$in": ["$$id", "$moods"] }
          }
        }
      ]
    }
  },

• $addFields to add count on the base of $size of array count that we got from above lookup

  {
    $addFields: {
      count: { $size: "$count" }
    }
  }
])

Playground