[MongoDB]: Aggregation with $unwind for querying subdocuments in an Array
[ root @ dbvesitydotcom : ~ ] mongo
MongoDB shell version: 2.6.5
connecting to: test
>
>
> use dbversity_db
switched to db dbversity_db
>
> db.mycol.insert({L1F1:”val1″,L1F2:[{L2F1:”val2″,L2F2:”val3″,L2F3:[{L3F1:”val50″, L3F2:”val51″,L3F3:”val52″},{L3F1:”val53″, L3F2:”val54″,L3F3:”val55″}]},{L2F4:”val4″,L2F5:”val5″}]})
WriteResult({ “nInserted” : 1 })
>
>
> db.mycol.find().pretty()
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : [
{
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : [
{
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
},
{
“L3F1” : “val53”,
“L3F2” : “val54”,
“L3F3” : “val55”
}
]
},
{
“L2F4” : “val4”,
“L2F5” : “val5”
}
]
}
>
>
> // If I want to query for dot notation of L1F2–>L2F3–>L3F1 (i.e., L1F2.L2F3.L3F1 subdocument) whose corresponding value is val50,
> // then the following query fails to give the exact value, additionally it also gives me another subdocument which is having “L3F1” : “val53” – because they’re sub-documents in an array
> db.mycol.find({“L1F2.L2F3.0.L3F1″:”val50”},{“L1F2.L2F3.L3F1.$”:1,_id:0}).pretty()
{
“L1F2” : [
{
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : [
{
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
},
{
“L3F1” : “val53”,
“L3F2” : “val54”,
“L3F3” : “val55”
}
]
}
]
}
>
> db.mycol.find({“L1F2.L2F3.0.L3F1″:”val50”},{“L1F2.L2F3.L3F1”:1,_id:0}).pretty()
{
“L1F2” : [
{
“L2F3” : [
{
“L3F1” : “val50”
},
{
“L3F1” : “val53”
}
]
},
{
}
]
}
>
>
To get only the exact matching document I found aggregation with $unwind is an option as below. Can any suggest any other solution, may array pointers or something.
$unwind :
Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.
{ $unwind: <field path> }
$unwind has the following behaviors:
If a value in the field specified by the field path is not an array, db.collection.aggregate() generates an error.
If you specify a path for a field that does not exist in an input document, the pipeline ignores the input document and will not output documents for that input document.
If the array holds an empty array ([]) in an input document, the pipeline ignores the input document and will not output documents for that input document.
> db.mycol.findOne()
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : [
{
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : [
{
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
},
{
“L3F1” : “val53”,
“L3F2” : “val54”,
“L3F3” : “val55”
}
]
},
{
“L2F4” : “val4”,
“L2F5” : “val5”
}
]
}
>
> db.mycol.aggregate({$unwind : “$L1F2” }).pretty()
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : {
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : [
{
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
},
{
“L3F1” : “val53”,
“L3F2” : “val54”,
“L3F3” : “val55”
}
]
}
}
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : {
“L2F4” : “val4”,
“L2F5” : “val5”
}
}
>
>
> db.mycol.aggregate({$unwind : “$L1F2” }, {$unwind : “$L1F2.L2F3”}).pretty()
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : {
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : {
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
}
}
}
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : {
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : {
“L3F1” : “val53”,
“L3F2” : “val54”,
“L3F3” : “val55”
}
}
}
>
> db.mycol.aggregate({$unwind : “$L1F2” }, {$unwind : “$L1F2.L2F3”}, { $match:{“L1F2.L2F3.L3F1″:”val50” }}).pretty()
{
“_id” : ObjectId(“5577ee5eb447fe152c5f71b5”),
“L1F1” : “val1”,
“L1F2” : {
“L2F1” : “val2”,
“L2F2” : “val3”,
“L2F3” : {
“L3F1” : “val50”,
“L3F2” : “val51”,
“L3F3” : “val52”
}
}
}
> db.mycol.aggregate({$unwind : “$L1F2” }, {$unwind : “$L1F2.L2F3”}, { $match:{“L1F2.L2F3.L3F1″:”val50” }}, { $project : { “L1F2.L2F3.L3F1”:1, _id:0}} ).pretty()
{ “L1F2” : { “L2F3” : { “L3F1” : “val50” } } }
>
>