MongoDB Aggregation. Check if nested array contains value

Question

I have such fields in entity:

private String userId;
private String username;
private Date created;
private List<Comment> comments = new ArrayList<>();

Comment have such fields :

private String userId;
private String username;
private String message;
private Date created;

I need to make aggregation, and receive something like this :

{
  "userId" : "%some_userId%",
  "date" : "%some_date%",
  "commentsQty" : 100,
  "isCommented" : true
}

My aggregation looks like this:

{ "aggregate" : "%entityName%" , "pipeline" : [
   { "$project" : { 
              "username" : 1 , 
              "userId" : 1 , 
              "created" : 1 , 
              "commentQty" : { "$size" : [ "$comments"]}}}]}

And it's working fine. But i need also to check, IF comments array contains some comment, with specific userId. I tried this one, but it fails to execute:

{ "aggregate" : "%entityName%" , "pipeline" : [
   { "$project" : { 
              "username" : 1 , 
              "userId" : 1 ,
              "created" : 1 , 
              "commentQty" : { "$size" : [ "$comments"]} ,
              "isCommented" : { "$exists" : [ "$comments.userId" , "5475b1e45409e07b0da09235"]}}}]}

with such message : Command execution failed: Error [exception: invalid operator '$exists']

How such check can be done?

UPD: Also tried operators $in and similar, but they valid for queering, not for aggregation.

Answer 1

with such message : Command execution failed: Error [exception: invalid operator '$exists']

Currently the $exists operator is not available in the aggregation pipeline.

edit:

Writing up a better answer:

You could check if any user has commented, by:

• using $setIntersection operator to get an array with the userId we are looking for, if the user has really commented on the post.
• apply $size operator to get the size of the resultant array.
• use $gt operator to check if the size is greater than 0.
• if it is, it means there exists one or more comments by the userId we are looking for, else not.

sample code:

var userIdToQuery = "2";
var userIdsToMatchAgainstComments = [ObjectId("5475b1e45409e07b0da09235")];

db.t.aggregate([
{$match:{"userId":userIdToQuery}},
{$project:{"userName":1,
           "created":1,
           "commentQty":{$size:"$comments"},
           "isCommented":{$cond:
                          [{$gt:[
                           {$size:
                             {$setIntersection:["$comments.userId",
                                     userIdsToMatchAgainstComments]}}
                          ,0]},
                          true,false]}}}
])

previous answer:

• Unwind comments.
• Project an extra field isCommented For each comments document, check if it has the userId that we are searching for, if it has the corresponding userId, then set the variable to 1 else 0.
• Group together the documents again, sum the value in isCommented, if it is > 0, the document with the user id is present in the group else not.
• Project the fields accordingly.

The Code:

{ "aggregate" : "%entityName%" , "pipeline" :[
    {$unwind:"$comments"},
    {$project:{
               "username":1,
               "userId":1,
               "created":1,
               "comments":1,
               "isCommented":{$cond:[{$eq:["$comments.userId",
                                           ObjectId("5475b1e45409e07b0da09235")
                                          ]
                                     },
                                     1,
                                     0]}}},
    {$group:{"_id":{"_id":"$_id",
                    "username":"$username",
                    "userId":"$userId",
                    "created":"$created"},
             "isCommented":{$sum:"$isCommented"},
             "commentsArr":{$push:"$comments"}}},
    {$project:{"comments":"$commentsArr",
               "_id":0,
               "username":"$_id.username",
               "userId":"$_id.userId",
               "created":"$_id.userId",
               "isCommented":{$cond:[{$eq:["$isCommented",0]},
                                  false,
                                  true]},
               "commentQty":{$size:"$commentsArr"}}},
    ]}

Answer 2

If you want to check for an exact single value (i.e. a specific logged-in userId), simply use the $in operator:

let loggedInUserId = "user1";

db.entities.aggregate([
{$project:{
    "userName": 1,
    "isCommentedByLoggedInUser": {
        $in : [ loggedInUserId, "$comments.userId" ]
    },
])

That's it.

Beware: If you don't expect every document to have comments array, wrap it with the $ifNull operator, and yield an empty array:

$in : [ loggedInUserId, { "$ifNull": [ "$comments.userId", [] ] } ]

---

BatScream's awesome answer is for checking against multiple values, when you need at least one out of many users (any logic) to fulfill the requirement. It is not required for your situation, as I see it, but it is a nice way of doing it.

So, BatScream mentioned the any logic, in the aggregational-way, and I'll continue with the all logic in the aggregational-way. For finding documents that have all specific users' comments, simply use the $setIsSubset operator:

let manyUsersIdsThatWeWantAll = ["user1", "user2", "user3"];

db.entities.aggregate([
{$project:{
    "userName": 1,
    "isCommentedByAllThoseUsers": {
        $setIsSubset: [
            manyUsersIdsThatWeWantAll, // the needle set MUST be the first expression
            "$comments.userId"
        ]
    },
])