How to get the count of element with non-empty-array-field when group in mongodb aggregate using Spring Data Mongo?

Question

I have the following documents in one collection named as mail_test. Some of them have a tags field which is an array:

/* 1 */
{
    "_id" : ObjectId("601a7c3a57c6eb4c1efb84ff"),
    "email" : "[email protected]",
    "content" : "11111"
}

/* 2 */
{
    "_id" : ObjectId("601a7c5057c6eb4c1efb8590"),
    "email" : "[email protected]",
    "content" : "22222"
}

/* 3 */
{
    "_id" : ObjectId("601a7c6d57c6eb4c1efb8675"),
    "email" : "[email protected]",
    "content" : "33333",
    "tags" : [ 
        "x"
    ]
}

/* 4 */
{
    "_id" : ObjectId("601a7c8157c6eb4c1efb86f4"),
    "email" : "[email protected]",
    "content" : "4444",
    "tags" : [ 
        "yyy", 
        "zzz"
    ]
}

There are two documents with non-empty-tags, so I want the result to be 2. I use the the following statement to aggregate and get the correct tag_count:

db.getCollection('mail_test').aggregate([{$group:{
    "_id":null,
    "all_count":{$sum:1},
    "tag_count":{"$sum":{$cond: [ { $ne: ["$tags", undefined] }, 1, 0]}}
    //if replace `undefined` with `null`, I got the tag_count as 4, that is not what I want
    //I also have tried `$exists`, but it cannot be used here.
}}])

and the result is:

{
    "_id" : null,
    "all_count" : 4.0,
    "tag_count" : 2.0
}

and I use spring data mongo in java to do this:

private void test(){
        Aggregation agg = Aggregation.newAggregation(
                Aggregation.match(new Criteria()),//some condition here
                Aggregation.group(Fields.fields()).sum(ConditionalOperators.when(Criteria.where("tags").ne(null)).then(1).otherwise(0)).as("tag_count")
                //I need an `undefined` instead of `null`,or is there are any other solution?
        );
        AggregationResults<MailTestGroupResult> results = mongoTemplate.aggregate(agg, MailTest.class, MailTestGroupResult.class);
        List<MailTestGroupResult> mappedResults = results.getMappedResults();
        int tag_count = mappedResults.get(0).getTag_count();
        System.out.println(tag_count);//get 4,wrong
    }

I need an undefined instead of null but I don't know how to do this,or is there are any other solution?

Why do you need undefined? I think both undefined and null should not be used for checking the non-existence of a field. — prasad_
– prasad_, Commented Feb 3, 2021 at 11:39
I agree , but I don't know any other solution.What is your suggestion? — Pengfei Ban
– Pengfei Ban, Commented Feb 3, 2021 at 11:53
I posted couple of possibilities as an answer - one of them can be used. — prasad_
– prasad_, Commented Feb 3, 2021 at 12:09

prasad_ · Accepted Answer · 2021-02-03 12:05:29Z

1

You can use Aggregation operators to check if the field tags exists or not with one of the following constructs in the $group stage of your query (to calculate the tag_count value):

"tag_count":{ "$sum": { $cond: [ { $gt: [ { $size: { $ifNull: ["$tags", [] ] }}, 0 ] }, 1, 0] }}

// - OR -

"tag_count":{ "$sum": { $cond: [ $eq: [ { $type: "$tags" }, "array" ] }, 1, 0] }

Both, return the same result (as you had posted).

answered Feb 3, 2021 at 12:05

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

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

6 Comments

Pengfei Ban Over a year ago

You are right. But how to write it in java ? I have tried this:

Aggregation.group(Fields.fields()).sum(ConditionalOperators.when(Criteria.where("tags.size").gt(0)).then(1).otherwise(0)).as("tag_count")

the result is correct but I still want to exclude the null values explicitly. I don't know how to use $ifNull here.

prasad_ Over a year ago

"...I still want to exclude the null values explicitly". Are you saying that a document can have tags: null?

Pengfei Ban Over a year ago

For now , no. But I want to make the situation a little more complex so that I can get more skills to do more complex aggregation in the future.I want to know how to translate mongodb script into java code strictly.

prasad_ Over a year ago

For now , no. In such case, the above two will work fine. You are using Spring Data MongoDB (which uses MongoDB Java driver) - lookup the API docs (the appropriate version). I generally, browse the APIs and figure by trying only.

prasad_ Over a year ago

Another option is, you can use a $facet stage with two facets - one with tags and one for all. The first facet can have a match stage, which will filter the documents without tags field. Then count the documents. The second facet just count the documents. It will be easy writing the code using Spring Data APIs. This eliminates the group stage and all its complex conditions.

|

Collectives™ on Stack Overflow

How to get the count of element with non-empty-array-field when group in mongodb aggregate using Spring Data Mongo?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related