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.

BatScream · Accepted Answer · 2016-02-03 19:16:43Z

11

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"}}},
    ]}

edited Feb 3, 2016 at 19:16

answered Nov 26, 2014 at 18:00

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Shatranaft Over a year ago

Thanks for answer. But seems that this not what i need. I need to check if array CONTAINS specific value. In my case i need to check, if "$comments" array contains comment with userId, say, "5475b1e45409e07b0da09235".

Shatranaft Over a year ago

I mean, isCommented field, it's - is commented by ME, mean, current user. So i need to check it during aggregation to avoid one more request to mongo...

Shatranaft Over a year ago

I finally implemented this in java code. Your solution is correct. Thanks a lot.

Minas Over a year ago

I think $cond is redundant, $gt returns boolean by it's own

Minas Over a year ago

@BatScream Btw thanks for the answer, I tried to do something similar without any luck for few hours, $setIntersection always returned empty array. Finally found out the reason, userId was stored as ObjectId and I compared it with a String. You answer helped me instantly haha

|

TechWisdom · Accepted Answer · 2018-04-01 16:26:57Z

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"
        ]
    },
])

Collectives™ on Stack Overflow

MongoDB Aggregation. Check if nested array contains value

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related