MongoDB aggregation. Check if nested array contains a value

Question

MongoDB aggregation. Check if nested array contains a value

I have fields like this in essence:

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 do an aggregation and get 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 works great. But I also need to check that the IF comments array contains some comment, with a specific userId. I tried this but it failed:

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

with a message like this: Command execution failed: Error [exception: invalid operator '$exists']

How can such a check be performed?

UPD: Operators $in

etc. have been tested as well, but they are valid for invocation and not for aggregation.

+3

spring-data-mongodb mongodb

Shatranaft 26 nov. 14 at 16:15

source to share

2 answers

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

let loggedInUserId = "user1";

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

What is it.

Beware: if you don't expect every document to have an array of comments, wrap it up with an operator $ifNull

and get an empty array:

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

BatScream's original answer is a multi-value validation when you need at least one of many users (any logic) to fulfill this requirement. This is not required for your situation as I see it, but it is a good way to do it.

So BatScream has mentioned any logic collectively, and I will continue with all logic collectively. To find documents containing comments from all specific users, simply use the operator $setIsSubset

:

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

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

0

TechWisdom 01 Apr '18 at 15:36

source to share

BatScream · Accepted Answer · 2014-11-26T18:00:18+0000

with this message: Command execution failed: Error [Exception: invalid operator '$ exists']

The operator is $exists

not currently available in the pipeline aggregation

.

change

Writing a better answer:

You can check if any user has commented:

using an $setIntersection

operator to get an array with userId

, which we are looking for if the user actually commented on the post.
apply the operator $size

to get the size of the resulting array.
use $gt

to check if the size is larger 0

.
If so, then there is one or more comments userId

that we are looking for, otherwise not.

example 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

extra field isCommented

For each comment document, check userId

if it has the one we are looking for, if it has a matching one userId

, then set the variable to 1

else 0

.
Group

together again document the docs, sum the value in isCommented

, if it is > 0

, doc with user id is present in else group not.
Project

respectively.

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

MongoDB aggregation. Check if nested array contains a value

More articles: