Reputation: 14590
MongoDB match documents that contain an array field with ALL elements that match the query
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?
Upvotes: 2
Views: 1508
Answers (2)
Reputation: 2029
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")
}
]
}
]
}
Upvotes: 1
Reputation: 14590
Found it! It was easy, just use $notoperator and check the opposite (< 5):
{ 'formats.prices': { $not: { $elemMatch: {price: { $lt: 5 } } } } }
Upvotes: 5
Related Questions
- MongoDB - Find document in which array contains any items in query array
- MongoDB query an array of documents for specific match
- Querying a collection for any documents that match any values listed in an array?
- find the document which equals any one of the array element query with a non array field
- Match all elements in document array with input array
- MongoDB - Select where array contains a document field
- MongoDB: query result match any value in array
- MongoDB. Find all document where element array have on of the needed element
- MongoDB - Find only documents where their arrays contains the query's array
- MongoDB match documents with all elements in a given array