MongoDB - Filter Array and Get distinct counts

Question

My MongoDB document looks like below:

{
    "_id" : ObjectId("5fb1828a6dbd2e5c533e2378"),
    "email" : "[email protected]",
    "fname" : "JOSE",
    "appt" : [ 
        {
            "date" : "12/04/2020",
            "time" : "0900",
        },
        {
            "date" : "12/05/2020",
            "time" : "1000",
        },
    ]
}

Both appt.date and appt.time are String!

I need to filter the records that contain array value appt.date: "12/04/2020". Then find all distinct appt.time values for given date along with its count.

I tried to use the pipeline aggregation but just cannot get it to work. How can I solve this in MongoDB 2.6.11?

turivishal · Accepted Answer · 2020-11-16 07:40:57Z

1

You can try,

$match appt.date condition to filter main document
$unwind deconstruct appt array
$match appt.date condition again to filter sub document
$group by null and make time unique using $addToSet array
$addFields to get count of total time

db.collection.aggregate([
  { $match: { "appt.date": "12/04/2020" } },
  { $unwind: "$appt" },
  { $match: { "appt.date": "12/04/2020" } },
  {
    $group: {
      _id: null,
      time: { $addToSet: "$appt.time" }
    }
  },
  {
    $project: {
      _id: 0,
      time: 1,
      count: { $size: "$time" }
    }
  }
])

Playground

edited Nov 16, 2020 at 7:40

answered Nov 16, 2020 at 7:21

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB - Filter Array and Get distinct counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related