MongoDB - aggregate over array of collection

Question

I have a collection which looks like this:

@Document(collection = "Contact")
public @Data class Contact {

    @Id
    private String id;
    private String institution;
    private List<ContactAddressWithProducts> addressesWithProducts;

and I will list all contacts which have more than one addressesWithProducts including the amount of addressesWithProducts.length.

This was my first try but it does not work:

db.Contact.aggregate([{$group: { _id: {institution: "$institution"}, count: {$sum: {addressesWithProducts}} }}, {$match: {count: {"$gt": 1} }} ]);

Does anyone have any idea how to solve it?

[EDIT] A collection looks like this:

{
"_id" : ObjectId("5a12c677c2dc334f8983a045"),
"_class" : "com.my.domain.dao.domain.Contact",
"institution" : "Contact name",
...
"addressesWithProducts" : [
    {
    "products" : [...]  
    "address" : DBRef("Address", ObjectId("59ede65fc2dc768853cc7843"))
    },
    {
    "products" : [...]  
    "address" : DBRef("Address", ObjectId("59ede6522222768853cc7843"))
    }
],


"creationDate" : ISODate("2017-11-20T12:11:00Z"),
"active" : true,
"address" : DBRef("Address", ObjectId("5a12c677c2dc334f8983a044")),
"tenant" : DBRef("Tenant", ObjectId("58500aed747a6cddb55ba094"))
}

And my expected output should look like this:

{ "_id" : { "institution" : "Contact name" }, "count" : 2 }
{ "_id" : { "institution" : "Contact name 123" }, "count" : 7 }
{ "_id" : { "institution" : "Contact name 5" }, "count" : 4 }
...

Could you show how your data looks ? and also the expected output — Ashh
– Ashh, Commented Oct 2, 2018 at 8:00
It should be db.Contact.aggregate([ { $group: { _id: { institution: "$institution" }, count: { $sum: 1 } }}, { $match: { count: { "$gt": 1 }}} ]) — Ashh
– Ashh, Commented Oct 2, 2018 at 8:20

matthPen · Accepted Answer · 2018-10-02 10:43:52Z

1

Change your query to the following :

db['Contact'].aggregate(
    [
        {
            $group: {
            _id: {institution: "$institution"},
            count:{$sum:{$size:{ $ifNull: [ "$addressesWithProducts", [] ] }}}
            }
        },
        {
            $match: {
            count: {"$gt": 1}
            }
        },
    ]
);

edited Oct 2, 2018 at 10:43

answered Oct 2, 2018 at 8:56

matthPen

4,3731 gold badge18 silver badges18 bronze badges

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

3 Comments

quma Over a year ago

thanks a lot!! I get the following error: assert: command failed: { "ok" : 0, "errmsg" : "The argument to $size must be an array, but was of type: missing", "code" : 17124, "codeName" : "Location17124" } : aggregate failed - can I handle this error

matthPen Over a year ago

You probably have document(s) without addressesWithProducts field. To avoid ths error, add a match stage to check if field exists. I edit my answer

matthPen Over a year ago

Instead of that (i forgot $exists is not available in aggregation), add $ifNull to threat those case (see edited answer)

Collectives™ on Stack Overflow

MongoDB - aggregate over array of collection

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related