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?

chridam · Accepted Answer · 2015-11-12 08:12:37Z

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.
@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 · Accepted Answer · 2015-11-12 08:12:42Z

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, 2015 at 8:12

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

3 Comments

Dmytro Shevchenko Over a year ago

An elegant solution!

Blakes Seven Over a year ago

@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.

user3142969 Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related