MongoDB: $ elemMatch embedded array error (projection)
> 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 of version 2.6.3 above. We really wanted to get the test result below, however it got an error request.
{
"_id" : ObjectId("5407f3c7e40dd5ddb98ab043"),
"b" : {
"a" : [
{
"k2" : "22"
}
]
}
}
We would like to keep this structure above, hopefully getting the result with One Query.
So please advise if there is another One request that we cannot find. If not, then you can also see if you want to provide a solution with this bug request in the future. Otherwise, we would like to have alternatives other than $ elemMatch (projection).
source to share
The bug says it - you can't use it $elemMatch
this way in 2.6. You can get what is essentially your desired output 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 ask the question why you are looking for such a result. You are looking for documents from seq : "1"
, and then you want to essentially search inside for a specific element of the array returned as a result. This indicates a circuit design problem. Maybe you want to have a document for every element of the arrays b.a
and a
and and denormalize fields like seq
in every document? I can't say anything for sure because you haven't given any details as to why you need to see the result as what you are asking for.
source to share
You are passing three arguments .find()
, but only to access them.
Definition:
db.collection.find (criteria, projection)
Original request
db.test.find({
"seq": "1"
}, {
"a": {
$elemMatch: {
"k2": "22"
}
},
"a.k2": 1
}).pretty();
Combining arguments 1 and 2 returns correct results.
Request 1:
db.test.find({
"seq": "1",
"a": {
$elemMatch: {
"k2": "22"
}
}
}, {
"a.k2": 1
}).pretty();
Output:
{
"_id": ObjectId("540cc98e1aaad58cc1b1ebdd"),
"a": [{
"k2": "12"
}, {
"k2": "22"
}]
}
Request 2:
db.test.find({
"seq": "1",
"b.a": {
$elemMatch: {
"k2": "22"
}
}
}, {
"b.a.k2": 1
}).pretty();
Output for request 2
{
"_id": ObjectId("540cc98e1aaad58cc1b1ebdd"),
"b": {
"a": [{
"k2": "12"
}, {
"k2": "22"
}]
}
}
source to share