How to return all documents where an array field's size is greater than a given number?

Question

I have a document like

{
    "_id": ObjectId('56a77bfae0ce9f6a738cb2b7'),
    "nameIdentity": [
        {
            "givenNameOne": "LATANYA",
            "givenNameThree": "BIZOR",
            "lastName": "BIZOR",
            "sourceReferenceId": [
                {
                    "sourceReferenceId": "56a77bfae0ce9f6a738cb2b5",
                    "sourceName": "A"
                },
                {
                    "sourceReferenceId": "56a77bfae0ce9f6a738cb2b5",
                    "sourceName": "B"
                }
            ]
        }
    ]
}

nameIdentity is an array and sourceReferenceId is an nested array inside nameIdentity. I am trying to get the documents whose sourceReferenceId size is greater than 2. I used i aggregation like this :

db.entity.aggregate(
   [
      {
         $project: {
            nameIdentity_count: {$size: "$nameIdentity.sourceReferenceId"}
         }
      },
      {
        "$match": {
            "nameIdentity_count": { "$gte": 2 }
         }
    }
   ]
)

this is not working as expected, it gives the document even with one soureReferenceId.Can anyone tell me where am i going wrong?

Solution 1

You need to use dot notation and the $exists operator. Also you don't need aggregation for this if all you want is find() those documents where "sourceReferenceId" size is greater than 2

db.collection.find( { "nameIdentity.sourceReferenceId.2": {  "$exists": true } } )

But if you really need to aggregate your data then:

db.collection.aggregate([
    { "$match": { "nameIdentity.sourceReferenceId.2": {  "$exists": true } } } 
])

Also you have a design problem because having a field where the value is always one element array doesn't make any sense. You should consider to change your document structure so that it looks like this:

{
    "_id": ObjectId('56a77bfae0ce9f6a738cb2b7'),
    "givenNameOne": "LATANYA",
    "givenNameThree": "BIZOR",
    "lastName": "BIZOR",
    "sourceReferenceId": [
        {
            "sourceReferenceId": "56a77bfae0ce9f6a738cb2b5",
            "sourceName": "A"
        },
        {
            "sourceReferenceId": "56a77bfae0ce9f6a738cb2b5",
            "sourceName": "B"
        }
    ]
}

And use the following queries depending on what you're trying to do.

db.collection.find( { "sourceReferenceId.2": {  "$exists": true } } )

or

db.collection.aggregate([
    { "$match": { "sourceReferenceId.2": {  "$exists": true } } } 
])

Solution 2

You need to use $size to get the size of items array and $gt to compare the size of items array to 0.

db.collection.find({"items":{$gt:[{$size:"items"},0]}})

Solution 3

This is how the query will be used:

db.<collection>.find( {<column> : {$exists:true}, $where:'this.<column>.length > 1'})

<collection>: Your collection

<column>: The name of your column

Comments

[Irfanuddin]

This does not work.