CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Night Walker
Night Walker

Reputation: 21260

Find documents with arrays that contain a document with a particular field

I want to find only documents that have all 'docs.foo' present in a array. In my example I should get only _id: 2 result: 

{  
    _id : 1,
    docs : [
        { bar : 2},
        { foo : 3, bar : 3}
    ]
},
{  
    _id : 2,
    docs : [
        { foo : 2, bar : 2},
        { foo : 3, bar : 3}
    ]
}

I thought about something like:

db.collection.find({'docs.foo': {$nin: [$exists: false]}})

but couldn't make it work.

Upvotes: 2

Views: 134

Answers (1)

Sede
Sede

Reputation: 61225

Using the $where operator.

db.collection.find(function() { 
    return this.docs.length === this.docs.filter(function(doc) {
        return typeof(doc.foo) !== "undefined" && doc.foo !== null ;}).length 
})

Another way to do this is to run two queries: One to retrieve the _id of all those documents that don't match your criteria using the distinct() method:

var unwantedIds = db.collection.distinct( "_id", { "docs": { "$elemMatch": { "foo": { "$exists": false } } } } );

Then use the $nin operator to return all those documents that match your criteria.

db.collection.find({ "_id": { "$nin": unwantedIds } } )

You can also use the .aggregate() method but this only work if you are on version 3.2 or newer because you need to use the $filter

First stage in the pipeline is the $match stage where you filter out those documents where the "foo" field is absent. This reduce the total number of documents that will be processed down the pipe. The next and last stage is the $redact stage. In this stage you need to use the $size operator to return the size of the the "docs" field and the size of the array of the sub-documents in where "foo" is present and return all those documents where the two values are equals.

db.collection.aggregate([
    { "$match": { "docs.foo": { "$exists": true } } }, 
    { "$redact": { 
        "$cond": [ 
            { "$eq": [ 
                { "$size": "$docs" }, 
                { "$size":  { 
                    "$filter": { 
                        "input": "$docs", 
                        "as": "doc", 
                        "cond": { 
                            "$ne": [ 
                                { "$ifNull": [ "$$doc.foo", null ] },
                                null 
                            ] 
                        } 
                    }
                }}
            ]}, 
            "$$KEEP", 
            "$$PRUNE"
        ]
    }}
])

Upvotes: 3

Related Questions