MongoDB match documents that contain an array field with ALL elements that match the query

Question

From the MongoDB doc for $elementMatch:

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

But how can I match documents that contain an array field with ALL elements that match the query?

For example I have documents like this:

{
    "_id": ObjectId("55c99649b8b5fc5b0a2f1c83"),
    "sku": "ed-39211",
    "created_at": ISODate("2015-08-11T06:29:29.139+0000"),
    "formats": [{
        "name": "thefile",
        "_id": ObjectId("55c99649f2e2d6353348ec9c"),
        "prices": [{
            "price": 4.49,
            "currency": "GBP",
            "territory": "GB",
            "_id": ObjectId("55c99649f2e2d6353348ec9f")
        }, {
            "price": 6.99,
            "currency": "USD",
            "territory": "US",
            "_id": ObjectId("55c99649f2e2d6353348ec9e")
        }, {
            "price": 6.99,
            "currency": "CHF",
            "territory": "CH",
            "_id": ObjectId("55c99649f2e2d6353348ec9d")
        }]
    }]
}

And I need to match all documents that have all the formats.prices.price > 5

If I use the following query:

{ 'formats.prices': { $elemMatch: { price: { $gte: 5 } } } }

That document will be matched because there is at least one price > 5

I also tried this but it doesn't seem to work:

{ 'formats.prices': { $all: { $elemMatch: {price: { $gte: 0.98 } } } } }

Is there a way to exclude that document looking at all prices an not at least one?

Answer 1

Found it! It was easy, just use $notoperator and check the opposite (< 5):

{ 'formats.prices': { $not: { $elemMatch: {price: { $lt: 5 } } } } }

Answer 2

You Can use Aggegation OR MAP REDUCE to achieve it :

First solution is using Map-Reduce :

I created a collection called "format" and inserted below data :

{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c83"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9c"),
                        "prices" : [
                                {
                                        "price" : 4.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}
{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                        "prices" : [
                                {
                                        "price" : 5.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}

Map_reduce :

db.format.mapReduce(
    function()
            {
            var doc  = {"_id" : this._id, "sku" : this.sku, "created_at" : this.created_at, "formats" : this.formats};
            var prices;
            var flag = 0;
            for ( var i = 0 ; i < doc.formats.length; i++)
            {
                prices = doc.formats[i].prices
                for ( var j =0 ; j < prices.length; j++)
                {
                    if( prices[j].price < 5)
                    {
                        flag = 1;
                        break;
                    }
                }
                if( flag == 1)
                doc.formats.splice(i,1);
            }
                if( doc.formats.length > 0 )
                emit( this._id, doc);
            },
    function(){},
    { "out": { "inline": 1 } }
)

Output :

{
        "results" : [
                {
                        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
                        "value" : {
                                "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
                                "sku" : "ed-39211",
                                "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
                                "formats" : [
                                        {
                                                "name" : "thefile",
                                                "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                                                "prices" : [
                                                        {
                                                                "price" : 5.49,
                                                                "currency" : "GBP",
                                                                "territory" : "GB",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                                        },
                                                        {
                                                                "price" : 6.99,
                                                                "currency" : "USD",
                                                                "territory" : "US",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                                        },
                                                        {
                                                                "price" : 6.99,
                                                                "currency" : "CHF",
                                                                "territory" : "CH",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                                        }
                                                ]
                                        }
                                ]
                        }

Second Solution using Aggregation :

Using aggregate operators $unwind and $size we can get the required result using below query :

After $unwind of "Formats" and "Formats.prices", size of the "Formats.prices" is taken and then a $match on the "prices" is done and again the new size is calculated for "Formats.prices".

If the size are same then all the "prices" in the "format" field are greater than 5 and the document will be projected.

db.format.aggregate([
{ $unwind: "$formats" },
{ $project : { _id : 1, sku : 1, created_at : 1, formats : 1, "size" : { $size : "$formats.prices" } } },
{ $unwind: "$formats.prices" },
{ $match: { "formats.prices.price" : { $gt:5 } } },
{ $group: { _id: { "name" : "$formats.name" , "_id" : "$formats._id", "id" : "$_id" }, prices : { $push: "$formats.prices" } , sku: { $first: "$sku" }, created_at : { $first: "$created_at" }, oldsize : { $first: "$size" } } },
{ $project: { _id : 1, prices : 1, sku : 1, created_at : 1, oldsize : 1, newsize : {$size: "$prices" } } },
{ $project: { _id : 1, prices : 1, sku : 1, created_at : 1, cmp_value: { $cmp: ["$oldsize", "$newsize"] } } },
{ $match: { cmp_value:{ $eq:0 } } },
{ $group : { _id : "$_id.id" , sku: { $first: "$sku" }, created_at : { $first: "$created_at" }, formats : { $push: { name : "$_id.name", "_id" : "$_id._id", prices: "$prices" } }  } }
                  ]).pretty() 

Output :

{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                        "prices" : [
                                {
                                        "price" : 5.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}