Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to group by counted elements using aggregation in MongoDB?

I have to write some aggregation using MQL. The case is:

  1. There is a chat application
  2. Collection contains info about: thread, sent message, sent time, read time

Sample records are shown below.


message_collection

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

[
    {
      "thread" : "war conference",
      "message" : "stop",
      "sentInMilli": 1696858510000, // oldest message in war conference
      "statuses": [ {"readInMilli": 1696858547857} ]
    },
    {
      "thread" : "economic conference",
      "message" : "welcome on economic conference",
      "sentInMilli": 1696858520000, // newest message in economic conference
      "statuses": [ {"readInMilli": 1696858547857} ]
    },
    {
      "thread" : "war conference",
      "message" : "the war",
      "sentInMilli": 1696858530000, // newest message in war conference
      "statuses": [ ]
    }
]

What I want to do is to display data about threads. To be more specific I need to know which thread have unread messages. As you can see in the example above, there are 2 threads: war conference and economic conference.

So after my supposable aggregation I want to get that kind of records:

[
  {
    "thread": "war conference",
    "timeInMilliOfNewestMessage": 1696858530000,
    "containsUnreadMessages": true
  },
  {
    "thread": "economic conference",
    "timeInMilliOfNewestMessage": 1696858520000,
    "containsUnreadMessages": false
  }
]

My current pseudo-query:

db.getCollection('message_collection').aggregate(
    [
        {
            "$match": { } // my other conditions...
        },
        {
            "$group": {
                "_id": "thread", 
                "timeInMilliOfNewestMessage": {"$max": "$sentInMilli"},
                "containsUnreadMessages": { 
                    "$count": {
                        "statuses": {
                            "$elemMatch": {
                                "readInMilli": { "$exists": false }
                            } 
                        }
                    }
                }
            }
        }
    ]
)

As you can see, I have an idea to firstly count all messages that are already read. Then I would like to convert it to boolean ones.

I got this error:

Unrecognized expression '$elemMatch'

I know this syntax is invalid but I don’t know why :/
Maybe what I want to achieve is impossible in the group section?
Could someone help me with that?

db.version()
4.0.0

>Solution :

As you are using MongoDB v4.0+, one of the possible solutions is to $min the array size of statuses and check for whether it is 0 or not

db.collection.aggregate([
  {
    "$group": {
      "_id": "$thread",
      "timeInMilliOfNewestMessage": {
        "$max": "$sentInMilli"
      },
      "containsUnreadMessages": {
        "$min": {
          $size: "$statuses"
        }
      }
    }
  },
  {
    "$addFields": {
      "containsUnreadMessages": {
        "$cond": {
          "if": {
            $eq: [
              "$containsUnreadMessages",
              0
            ]
          },
          "then": true,
          "else": false
        }
      }
    }
  }
])

Mongo Playground

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading