MongoDB project the documents with count greater than 2 [duplicate]

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?

Answer 1

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
}

Answer 2

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.