MongoDB : Get documents whose length of array >= 2

Question

Asking for help to fix this mongoDb/mongoose query. It should do :

evaluate the quantity of array elements of 'host'
return only documents that have more than 2 elements into array
finally, the query should get back all documents/objects of DB with ALL FIELDS following the paraments above.

Sample Document :

{
    "_id" : ObjectId("XXXXXXXXX"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY", 
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z",
}

Attempt 1 :

db.getCollection('emails').find({'sentEmail.status': false}, {$where:'sentEmail.host >= 2'})

Problem : It is not return ALL fields, it returns only ID

Attempt 2 :

db.getCollection('emails').find({'sentEmail.host.1': {$exists:true}, 'sentEmail.status': false})

Problem : It returns only the first object found.

Please translate it to English to get an answer!!

whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul

2020-01-27 17:41:38 +00:00
Commented Jan 27, 2020 at 17:41 — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Jan 27, 2020 at 17:41
@srinivasy done

McFly
– McFly

2020-01-27 18:04:06 +00:00
Commented Jan 27, 2020 at 18:04 — McFly
– McFly, Commented Jan 27, 2020 at 18:04

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-01-27 18:34:01Z

You can try this :

db.getCollection('emails').find({$expr :{$and : [{$eq :['$sentEmail.status', false]},
                                            {$gte: [{$size:'$sentEmail.host'},2]}]}})

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e2f26ead02e05b6948bc302"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY"
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

/* 2 */
{
    "_id" : ObjectId("5e2f26f1d02e05b6948bc3b5"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.YYYY"
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

/* 3 */
{
    "_id" : ObjectId("5e2f26ffd02e05b6948bc4c8"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY", 
            "smtp.ZZZZ"
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

/* 4 */
{
    "_id" : ObjectId("5e2f2bd2d02e05b6948c2c58"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY", 
            "smtp.ZZZZ"
        ],
        "status" : true,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

Result :

/* 1 */
{
    "_id" : ObjectId("5e2f26ead02e05b6948bc302"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY"
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

/* 2 */
{
    "_id" : ObjectId("5e2f26ffd02e05b6948bc4c8"),
    "content" : "ASdkJAHKDA",
    "time" : ISODate("2020-01-24T17:20:00.023Z"),
    "sentEmail" : {
        "host" : [ 
            "smtp.XXX", 
            "smtp.YYYY", 
            "smtp.ZZZZ"
        ],
        "status" : false,
        "date" : ISODate("2020-01-27T14:36:08.311Z")
    }
}

If did not return document with "_id" : ObjectId("5e2f26f1d02e05b6948bc3b5") because size of sentEmail.host < 2 && "_id" : ObjectId("5e2f2bd2d02e05b6948c2c58") is not returned cause it has sentEmail.status: true.

Great! but you missed the sentEmail.status : false. I need that condition too. Could you help with it?

Collectives™ on Stack Overflow

MongoDB : Get documents whose length of array >= 2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related