2

In one of my project, I need to get a distinct count for a combination of two fields in my database collection. I will give a brief description about the structure of my collection below.

It is basically a discussion system, it has a list of rooms where people send messages. I am giving a test data from which I require a count

/************* First Chat **********************************/
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c01"),
    "users"     : ObjectId("69a7a7c462e1e20c15000001"),
    "rooms"     : ObjectId("89ab16244b964729b3154a11"),
    "message"   : "Hello"
},
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c02"),
    "users"     : ObjectId("69a7a7c462e1e20c15000001"),
    "rooms"     : ObjectId("89ab16244b964729b3154a11"),
    "message"   : "Hello test"
},
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c03"),
    "users"     : ObjectId("69a7a7c462e1e20c15000002"),
    "rooms"     : ObjectId("89ab16244b964729b3154a11"),
    "message"   : "I am here"
},
/************* second Chat **********************************/
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c04"),
    "users"     : ObjectId("69a7a7c462e1e20c15000001"),
    "rooms"     : ObjectId("89ab16244b964729b3154a12"),
    "message"   : "Hello New"
},
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c05"),
    "users"     : ObjectId("69a7a7c462e1e20c15000001"),
    "rooms"     : ObjectId("89ab16244b964729b3154a12"),
    "message"   : "Hello test new"
},
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c06"),
    "users"     : ObjectId("69a7a7c462e1e20c15000002"),
    "rooms"     : ObjectId("89ab16244b964729b3154a12"),
    "message"   : "I am here!!!"
},
{
    "_id"       : ObjectId("8a1370e00d5b6e0a2a865c07"),
    "users"     : ObjectId("69a7a7c462e1e20c15000003"),
    "rooms"     : ObjectId("89ab16244b964729b3154a12"),
    "message"   : "I am 3rd user"
}

It has 2 rooms ObjectId("59ef16244b964729b3154a11") and ObjectId("59ef16244b964729b3154a12")

Room 1 has 2 active members ObjectId("59a7a7c462e1e20c15000001") and ObjectId("59a7a7c462e1e20c15000002")

Room 2 has 3 active members ObjectId("59a7a7c462e1e20c15000001"), ObjectId("59a7a7c462e1e20c15000002")and ObjectId("59a7a7c462e1e20c15000003")

I need the result like How may rooms a user participated So for the above example I need result as follows

  1. User 1 ObjectId("59a7a7c462e1e20c15000001") : count 2
  2. User 2 ObjectId("59a7a7c462e1e20c15000002") : count 2
  3. User 3 ObjectId("59a7a7c462e1e20c15000003") : count 1
Team Techi
  • 305
  • 3
  • 15
  • 1
    `db.collection.aggregate([{ "$group": { "_id": { "user": "$user", "room": "$room" } }},{ "$group": { "_id": "$_id.user", "count": { "$sum": 1 } } }])` So get the distinct combination, and then simply group by the user only in order to count the distinct rooms. – Neil Lunn Nov 15 '17 at 08:02
  • Perfect solution! Thank you – Team Techi Nov 15 '17 at 08:29

1 Answers1

2

You can use db.collection.distinct() and specify the query as the second argument:

db.getCollection('message').distinct('rooms', { users: ObjectId('your-user-id')})

If you need to get this for each user, you can use aggregation:

db.getCollection('messages').aggregate([
  { $group: { _id: "$users", rooms: { $addToSet: "$rooms" } } }
]);

Here we are grouping the documents by users field, and we're using $addToSet to select distinct values from $rooms field, and the response of this query would be the array of documents with the fields _id (the user's ID) and rooms (the array of ObjectIds of rooms where this user has posted).

serge1peshcoff
  • 4,342
  • 11
  • 45
  • 76