Count Documents Matching Multiple Array Criteria

Question

Schema is:

{ 
"_id" : ObjectId("594b7e86f59ccd05bb8a90b5"), 
"_class" : "com.notification.model.entity.Notification", 
"notificationReferenceId" : "7917a5365ba246d1bb3664092c59032a", 
"notificationReceivedAt" : ISODate("2017-06-22T08:23:34.382+0000"), 
"sendTo" : [
    {
        "userReferenceId" : "check", 
        "mediumAndDestination" : [
            {
                "medium" : "API",
                "status" : "UNREAD"
            }
        ]
    }
]
}
{ 
"_id" : ObjectId("594b8045f59ccd076dd86063"), 
"_class" : "com.notification.model.entity.Notification", 
"notificationReferenceId" : "6990329330294cbc950ef2b38f6d1a4f",
"notificationReceivedAt" : ISODate("2017-06-22T08:31:01.299+0000"), 
"sendTo" : [
    {
        "userReferenceId" : "check",
        "mediumAndDestination" : [
            {
                "medium" : "API",
                "status" : "UNREAD"
            }
        ]
    }
]
}
{ 
"_id" : ObjectId("594b813ef59ccd076dd86064"), 
"_class" : "com.notification.model.entity.Notification", 
"notificationReferenceId" : "3c910cf5fcec42d6bfb78a9baa393efa", 
"notificationReceivedAt" : ISODate("2017-06-22T08:35:10.474+0000"), 
"sendTo" : [
    {
        "userReferenceId" : "check", 
        "mediumAndDestination" : [
            {
                "medium" : "API", 
                "status" : "UNREAD"
            }
        ]
    }, 
    {
        "userReferenceId" : "hello", 
        "mediumAndDestination" : [
            {
                "medium" : "API",
                "status" : "READ"
            }
        ]
    }
]
}

I want to count a user notifications based on statusList which is a List. I used mongoOperations to make a query:

Query query = new Query();
    query.addCriteria(Criteria.where("sendTo.userReferenceId").is(userReferenceId)
    .andOperator(Criteria.where("sendTo.mediumAndDestination.status").in(statusList)));

long count = mongoOperations.count(query, Notification.class);

I realise I'm doing it wrong because I am getting count as 1 when I query for user with reference ID hello and statusList with single element as UNREAD.

How do I perform an aggregated query on array element?

Well yes the "query" is incorrect, however.It's also not clear from your question as to "which" thing you want to "count" here. Is it A. Documents that match B. Total Number of items in `"sendTo"` that match, or C. Total number of items from all `"mediumAndDestination"` which matched both conditions? So the **none** of the documents in your sample match that combination of conditions, but it's also not clear if that is basically what you are asking. — Neil Lunn, Jun 23 '17 at 07:38
@NeilLunn Say, I want to count a user notifications which are `UNREAD`. User is identified by `userReferenceId ` and notification status is identified by `status `. — Shubham A., Jun 23 '17 at 07:44
Which one of A, B or C in my list is a "user notifications"? Is it "A" the documents? Are you then expecting `0` with the correct query? Because as I see it, none of the documents here match that criteria. — Neil Lunn, Jun 23 '17 at 07:48
@NeilLunn Root object is a notification. I want to get `check's` notification which are `UNREAD`. It is B. So, I want a count for only 1 user of how many notifications has he `UNREAD`. — Shubham A., Jun 23 '17 at 07:54
Please listen. None of the documents in your question match those conditions. There is **ONE** where it's `"hello"` and `"READ"` but there is **NO** `"hello"` AND `"UNREAD"`. All I see is the last document with the last array item matching. Inside both arrays. So it's either **ZERO** or the criteria you have given are incorrect. — Neil Lunn, Jun 23 '17 at 07:57
@NeilLunn I see. I think I was being too specific. It was just about getting correct count of notifications based on a status for a particular user. — Shubham A., Jun 23 '17 at 08:15

score 1 · Accepted Answer · answered Jun 23 '17 at 08:02

The query needs $elemMatch in order to actually match "within" the array element that matches both criteria:

   Query query = new Query(Criteria.where("sendTo")
       .elemMatch(
           Criteria.where("userReferenceId").is("hello")
             .and("mediumAndDestination.status").is("UNREAD")
       ));

Which essentially serializes to:

  { 
    "sendTo": {
      "$elemMatch": {
        "userReferenceId": "hello",
        "mediumAndDestination.status": "UNREAD"
      }
    }
  }

Note that in your question there is no such document, the only matching thing with "hello" actually has the "status" of "READ". If I supply those criteria instead:

  { 
    "sendTo": {
      "$elemMatch": {
        "userReferenceId": "hello",
        "mediumAndDestination.status": "READ"
      }
    }
  }

Then I get the last document:

{
    "_id" : ObjectId("594b813ef59ccd076dd86064"),
    "_class" : "com.notification.model.entity.Notification",
    "notificationReferenceId" : "3c910cf5fcec42d6bfb78a9baa393efa",
    "notificationReceivedAt" : ISODate("2017-06-22T08:35:10.474Z"),
    "sendTo" : [ 
        {
            "userReferenceId" : "check",
            "mediumAndDestination" : [ 
                {
                    "medium" : "API",
                    "status" : "UNREAD"
                }
            ]
        }, 
        {
            "userReferenceId" : "hello",
            "mediumAndDestination" : [ 
                {
                    "medium" : "API",
                    "status" : "READ"
                }
            ]
        }
    ]
}

But with "UNREAD" the count is actually 0 for this sample.

This worked. Nice explanation as well, especially the `essentially serializes` section. — Shubham A., Jun 23 '17 at 08:16
@ShubhamA. It's the `$elemMatch` part that you need to learn to use with **more than one** condition for an element in an array. Without that, you are actually looking at **ALL** the array values at the provided field paths. That's the difference in the query. So we are just comparing "one element at a time". You can get the JSON serialize from any `Criteria` using `.getCriteriaObject()`. Good for comparison. Also if you "found it helpful", then [I think theres an action for that here.](https://meta.stackexchange.com/questions/173399/how-to-upvote-on-stack-overflow) — Neil Lunn, Jun 23 '17 at 08:21

Count Documents Matching Multiple Array Criteria

1 Answers1