> db.test.insert(
{
"seq" : "1",
"a" : [
{
"k1" : "11",
"k2" : "12"
},
{
"k1" : "21",
"k2" : "22"
}
],
"b" : {
"a" : [
{
"k1" : "11",
"k2" : "12"
},
{
"k1" : "21",
"k2" : "22"
}
]
}
}
)
> db.test.find({ "seq" : "1" }, { "a" : { $elemMatch : { "k2" : "22" } }, "a.k2" : 1 }).pretty();
{
"_id" : ObjectId("5407f3c7e40dd5ddb98ab043"),
"a" : [
{
"k2" : "22"
}
]
}
> db.test.find({ "seq" : "1" }, { "b.a" : { $elemMatch : { "k2" : "22" } }, "b.a.k2" : 1 }).pretty();
error: {
"$err" : "Can't canonicalize query: BadValue Cannot use $elemMatch projection on a nested field.",
"code" : 17287
}
Please find the test result on 2.6.3 version above. We actually wanted to have the test result below, however, received an error query with it.
{
"_id" : ObjectId("5407f3c7e40dd5ddb98ab043"),
"b" : {
"a" : [
{
"k2" : "22"
}
]
}
}
We would like to keep this structure above, hopefully to get the result with One Query.
Therefore, please advise if there's other One Query available that we cannot find. If not, then, we’d also like to know if you are willing to provide the solution with this error query in the future. Otherwise, we would like to have alternatives other than $elemMatch(projection).
The error says it all- you can't use $elemMatch this way in 2.6. You can get what is essentially your desired result with a simple aggregation pipeline:
> db.test.aggregate([
{ "$match" : { "seq" : "1" } },
{ "$unwind" : "$b.a" },
{ "$match" : { "b.a.k2" : "22" } },
{ "$project" : { "_id" : 1, "b.a.k2" : 1 } }
])
{ "_id" : ObjectId("5409f00ad5a65fc7ef57f67e"), "b" : { "a" : { "k2" : "22" } } }
But I have to question why you are looking for a result like this. You're searching for documents with seq : "1" and then you want to essentially search inside for a specific element of an array to return as the result. This hints at a schema design problem. Maybe you want to have a document per element of the b.a and a arrays and denormalize fields like seq into each document? I can't say anything confidently because you've given no details of why you need to see a result like what you're asking for.