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
– Neil Lunn, Commented Jun 23, 2017 at 7: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.
– Shubham A., Commented Jun 23, 2017 at 7: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
– Neil Lunn, Commented Jun 23, 2017 at 7: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.
– Shubham A., Commented Jun 23, 2017 at 7: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
– Neil Lunn, Commented Jun 23, 2017 at 7:57

Neil Lunn · Accepted Answer · 2017-06-23 08:02:01Z

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.
@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.

Collectives™ on Stack Overflow

Count Documents Matching Multiple Array Criteria

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related