I have already gone through this popular question. None of the described solutions really solve my problem. I have been going through other posts too, in vain.
I want to find specific projected fields (The projected fields could be part of arrays) of documents with query which consists of conditions on objects at various depths of nested arrays. The nesting of arrays in my documents could go up to 5 levels. I am using $match, $and for combining all my conditions and $elemMatch for nested arrays. The querying part by itself works fine and it finds the right document. The problem however is while projecting the required fields (Some of these fields belong to deeply nested arrays)
Solutions that do not work for my situation and why :
$filter: Available from version 3.2. I am using version 3.0. $filter is not an option.$unwind: My nested arrays (Array3 and Array6 in my example below) has 8000 array elements. By definition of$unwind, it will create a stream of separate documents with data duplicated. This is not something we would want to do as the documents are pretty big and duplication, thousands of times does not seem like a good idea. If I am missing something with$unwindplease let me know.$elemMatchand$ operator: Both return only one matched object in the array. I want multiple objects in the array that matched certain conditions to be projected.$redact: I tried the approach described in this answer. Not sure if it is the right approach to take in nested arrays. For simple examples with no nested arrays, it worked (to some extent)
Below is an example document, expected results and some things which came close to solving the issue but not quite.
Example document (Actual document in my collection. Names changed and array elements reduced. I am not making this up. The data I am working with is really this nested. Also the innermost arrays could stretch to have 8000 elements) :
{
"Name" : "fa191a58-3c8b-4533-9545-3379e996c0b7",
"Type" : "67448a7f-c514-4dc4-9836-4be6f54572a7",
"DataObj" : {
"Community" : [
{
"Name" : "5e6ac308-5e14-4c83-8111-55f87f3bab17",
"DataPoints" : {
"Person" : {
"ItemCount" : 777,
"NameCount" : NumberInt(999),
"LevelCount" : NumberInt(999)
},
"Houses" : [
{
"DoorNumber" : NumberInt(4),
"MatNumber" : 777,
"Person" : {
"ItemCount" : NumberInt(999),
"NameCount" : NumberInt(999),
"OneTwoDetails" : {
"WholeObj" : {
"OneTwo123" : {
"One1X" : 999.9,
"One2X" : 999.9,
"One3X" : 999.9
}
},
"Houses" : [
{
"DoorNumber" : NumberInt(999),
"MatNumber" : NumberInt(999),
"OneTwo123" : {
"One1X" : 999.9,
"One2X" : 999.9,
"One3X" : 999.9
},
"Rooms" : [
{
"LengthX" : NumberInt(999),
"LengthY" : NumberInt(999),
"OneTwo123" : {
"One1X" : 777,
"One2X" : 999.9,
"One3X" : 999.9
}
},
{
"LengthX" : NumberInt(999),
"LengthY" : NumberInt(999),
"OneTwo123" : {
"One1X" : 999.9,
"One2X" : 999.9,
"One3X" : 999.9
}
}
]
}
]
},
"ApplianceCount" : NumberInt(999),
"UtilityPowerCount" : NumberInt(999),
"UtilityPowerDetails" : [
{
"Name" : "d0f587f8-0a5a-4cd8-aa40-b8ca6e725f27",
"Range" : 999.9
},
{
"Name" : "76626240-2f58-44d0-8a53-0c161cd6ce58",
"Range" : 999.9
}
]
}
}
]
},
"DataSearching" : {
"SearchSequence" : NumberInt(999),
"SearchedDetails" : {
"TagX" : 999.9,
"TagY" : 999.9
},
"Houses" : [
{
"Sequence" : NumberInt(4),
"SearchedDetails" : {
"TagX" : 999.9,
"TagY" : 999.9
},
"Rooms" : [
{
"Sequence" : NumberInt(999),
"Clauses" : [
{
"Value" : 777,
"IsLimited" : true
},
{
"Value" : 999.9,
"IsLimited" : true
}
]
}
]
}
]
}
}
]
}
}
Query : AND of all the below
- "DataObj.Community.DataPoints.Person.ItemCount" Equals 777
- "DataObj.Community.DataPoints.Houses.MatNumber" Equals 777
- "DataObj.Community.DataPoints.Houses.Person.OneTwoDetails.Houses.Rooms.OneTwo123.One1X" Equals 777
- "DataObj.Community.DataSearching.Houses.Rooms.Clauses.Value" Equals 777
Projection :
- "DataObj.Community.DataPoints.Person.ItemCount"
- "DataObj.Community.DataPoints.Houses.MatNumber"
- "DataObj.Community.DataPoints.Houses.Person.OneTwoDetails.Houses.Rooms.OneTwo123.One1X"
- "DataObj.Community.DataSearching.Houses.Rooms.Clauses.Value"
Expected Result :
{
"DataObj" : {
"Community" : [
{
"DataPoints" : {
"Person" : {
"ItemCount" : 777
},
"Houses" : [
{
"MatNumber" : 777,
"Person" : {
"OneTwoDetails" : {
"Houses" : [
{
"Rooms" : [
{
"OneTwo123" : {
"One1X" : 777
}
}
]
}
]
}
}
}
]
},
"DataSearching" : {
"Houses" : [
{
"Rooms" : [
{
"Clauses" : [
{
"Value" : 777
}
]
}
]
}
]
}
}
]
}
}
I achieved partial success with the below approach, but I don't know how to make this work for nested arrays.
Document without nested array :
{
"_id" : ObjectId("57154af04b42aa1fd05fe9fe"),
"Element1" : "5e6ac308-5e14-4c83-8111-55f87f3bab17",
"Element2" : NumberInt(1),
"Array1" : [
{
"Array1Elem1" : NumberInt(666),
"Array1Elem2" : 1999.0,
"Array1Elem3" : 999,
"Array1Elem4" : 999
},
{
"Array1Elem1" : NumberInt(999),
"Array1Elem2" : 999.9,
"Array1Elem3" : 999,
"Array1Elem4" : 999
},
{
"Array1Elem1" : NumberInt(999),
"Array1Elem2" : 999.9,
"Array1Elem3" : 999,
"Array1Elem4" : 999
},
{
"Array1Elem1" : NumberInt(999),
"Array1Elem2" : 999.9,
"Array1Elem3" : 999,
"Array1Elem4" : 999
},
{
"Array1Elem1" : NumberInt(444),
"Array1Elem2" : 2999,
"Array1Elem3" : 999,
"Array1Elem4" : 999
}
]
}
Approach taken (Found in another StackOverflow Question) :
db.collectionName.aggregate(
{ "$match" : {
$and : [
{ Array1 : {
$elemMatch: {
"Array1Elem1": {$lt : 999},
"Array1Elem2" : {$gt : 1200}
}
}
},
{
// other conditions
}
]
}
},
{ "$project" : {
"Array1" : {
"$setDifference" : [
{ "$map" : {
"input" : "$Array1",
"as" : "element",
"in" : {
"$cond" : [
{ "$and" : [
{"$lt" : ["$$element.Array1Elem1", 999]},
{"$gt" : ["$$element.Array1Elem2", 1200]}
]
},
{"$cond" : [1, "$$element", 0]},
false
]
}
}},
[false]
]
}
}
})
Result (Almost what I need. Does not exactly return the asked projected fields, but at least it returns only the array elements which match the condition. I can work with that) :
{
"_id" : ObjectId("57154af04b42aa1fd05fe9fe"),
"Array1" : [
{
"Array1Elem1" : NumberInt(666),
"Array1Elem2" : 1999.0,
"Array1Elem3" : 999,
"Array1Elem4" : 999
},
{
"Array1Elem1" : NumberInt(444),
"Array1Elem2" : 2999,
"Array1Elem3" : 999,
"Array1Elem4" : 999
}
]
}
If I can expand the behaviour to nested arrays, I can use that. If there is any other way to achieve projection with nested arrays, please discuss.
