MongoDB project the documents with count greater than 2

Question

I have a collection like

{
    "_id": "201503110040020021",
    "Line": "1", // several documents may have this Line value
    "LineStart": ISODate("2015-03-11T06:49:35.000Z"),
    "SSCEXPEND": [{
            "Secuence": 10,
            "Title": 1,
        },
        {
            "Secuence": 183,
            "Title": 613,
        },
        ...
    ],

} {
    "_id": "201503110040020022",
    "Line": "1", // several documents may have this Line value
    "LineStart": ISODate("2015-03-11T06:49:35.000Z"),
    "SSCEXPEND": [{
            "Secuence": 10,
            "Title": 1,
        },

    ],

}

SSCEXPEND is an array. I am trying to count the size of SSC array and project if the count is greater than or equal to 2. My query is something like this

db.entity.aggregate(
   [
      {
         $project: {
            SSCEXPEND_count: {$size: "$SSCEXPEND"}
         }
      },
      {
        $match: {
            "SSCEXPEND_count2": {$gte: ["$SSCEXPEND_count",2]}
         }
      }
   ]
)

I am expecting the output to be only the the first document whose array size is greater than 2.

Project part is working fine and I am able to get the counts but I need to project only those which has count greater than or equal to two but my match part is not working. Can any one guide me as where am I going wrong?

score 8 · Accepted Answer · answered Nov 12 '15 at 08:12

You need to project the other fields and your $match pipeline will just need to do a query on the newly-created field to filter the documents based on the array size. Something like the following should work:

db.entity.aggregate([
    {
        "$project": {
            "Line": 1,
            "LineStart": 1, "SSCEXPEND": 1,
            "SSCEXPEND_count": { "$size": "$SSCEXPEND" }
         }
    },
    {
        "$match": {
            "SSCEXPEND_count": { "$gte": 2 }
         }
    }
])

Sample Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "201503110040020021",
            "Line" : "1",
            "LineStart" : ISODate("2015-03-11T06:49:35.000Z"),
            "SSCEXPEND" : [ 
                {
                    "Secuence" : 10,
                    "Title" : 1
                }, 
                {
                    "Secuence" : 183,
                    "Title" : 613
                }
            ],
            "SSCEXPEND_count" : 2
        }
    ],
    "ok" : 1
}

You should use the `$redact` if you want to use aggregation. — styvane, Nov 12 '15 at 14:17
@user3100115 You "should" use `$match` if you want to use aggregation still. No need to force functions just to determine if there are a minimal level of array elements. `$redact` still forces a collection scan, just like `$where` does. — Blakes Seven, Nov 13 '15 at 06:05

score 8 · Answer 2 · answered Nov 12 '15 at 08:12

8

This is actually a very simple query, where the trick is to use a property of "dot notation" in order to test the array. All you really need to ask for is documents where the array index of 2 $exists, which means the array must contain 3 elements or more:

db.entity.find({ "SSCEXPEND.2": { "$exists": true } })

It's the fastest way to do it and can even use indexes. No need for calculations in aggregation operations.

answered Nov 12 '15 at 08:12

Blakes Seven

49,422
14
129
135

An elegant solution! – Dmytro Shevchenko Nov 13 '15 at 07:13
@DmytroShevchenko And yet ( "for some unknown reason" ) the OP chose to accept this first and then recanted. It is the efficient method that people "should" follow, as I also pointed out with the marked duplicate that has a top voted answer saying exactly the same thing ( that I was not aware of on posting but then found ). It's a simple principle that if the `n-1` element actually exists, then the array must be of at least that length. Funny enough there is also another answer posted on the duplicate question that suggests the same size calcuation as accepted. But that is clearly wrong to do. – Blakes Seven Nov 13 '15 at 07:23
This is a good answer for array that contain 3 elements or more, but, how can I do the opposite? I mean if want less than 3 elements. – user3142969 Nov 26 '16 at 03:18

MongoDB project the documents with count greater than 2

2 Answers2

Linked