Count array documents in mongodb collection

Question

i have a entity collection like this:

 {
        "_id" : ObjectId("55f93cedc4fd0e1f309aea64"),
        "entityType" : "1",
        "identifierIdentity" : [
                {
                        "identifierTypeCode" : "NPI",
                        "identifierValue" : "111"
                },
                {
                        "identifierTypeCode" : "NPI",
                        "identifierValue" : "123"
                },
                {
                        "identifierTypeCode" : "NPI",
                        "identifierValue" : "141"
                },
                {
                        "identifierTypeCode" : "SSN",
                        "identifierValue" : "155"
                }
        ]
}
{
        "_id" : ObjectId("55f93cedc4fd0e1f309aea65"),
        "entityType" : "2",
        "identifierIdentity" : [
                {
                        "identifierTypeCode" : "NPI",
                        "identifierValue" : "111"
                },
                {
                        "identifierTypeCode" : "NPI",
                        "identifierValue" : "123"
                },
                {
                        "identifierTypeCode" : "SSN",
                        "identifierValue" : "155"
                }
        ]
}
{
        "_id" : ObjectId("55f93cedc4fd0e1f309aea66"),
        "entityType" : "3",
        "identifierIdentity" : [
                {
                        "identifierTypeCode" : "SSN",
                        "identifierValue" : "111"
                },
                {
                        "identifierTypeCode" : "SSN",
                        "identifierValue" : "123"
                }
        ]
}

in the above identifierIdentity is an array of documents.

I am trying to get the entity count where "identifierTypeCode" : "NPI" is greater than or equal to two.

I am able to get this using java code but i have millions of records which is taking a lot of time. I want to know whether is their a way i can achieve this in a single query.

possible duplicate of Mongo - get documents with nested objects matching count condition — Neo-coder
– Neo-coder, Commented Sep 16, 2015 at 10:20

Blakes Seven · Accepted Answer · 2015-09-16 10:47:39Z

The $redact aggregate operator will make short work of this, coupled with a match for arrays that have at least two elements to trim thngs down:

    db.collection.aggregate([
    { "$match": {
        "identifierIdentity.identifierTypeCode": "NPI",
        "identifierIdentity.1": { "$exists": true }
    }},
    { "$redact": {
        "$cond": {
            "if": { 
                "$gte":  [ 
                    { "$size": { "$setDifference": [
                        { "$map": {
                            "input": "$identifierIdentity",
                            "as": "el",
                            "in": {
                                "$cond": {
                                    "if": { "$eq": ["$$el.identifierTypeCode", "NPI"] },
                                    "then": "$$el",
                                    "else": false
                                }
                            }
                        }},
                        [false]
                    ] } },
                    2
                ]
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

Basically match first to cut down the possible documents to procces, the the $redact does another logical match by filtering out the matches from the array and counting the size of the result. Where greater or equal to 2, then you keep the result, or otherwise discard it.

Collectives™ on Stack Overflow

Count array documents in mongodb collection

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related