* It is quite common to have nested array in documents in MongoDB.
* It is also quite common to have to "flatify" those arrays in queries.
Here is an example where we have a collection with consultants and their professional experiences.
```
consultant [{
name:"Toto",
age: 25,
experiences: [{
role: "Sofware Engineer",
from: "2010-01-01",
to: "2012-01-01",
company: "CompanyA"
}, {
role: "Data Analyst",
from: "2018-01-01",
to: "2020-01-01",
company: "CompanyB"
}]
}]
```
If we would like to list all experiences at a company during a certain period of time, we would have to do an aggregate query with 3 steps that are sometimes error prone:
- $match with $elemMatch
- $unwind
- same $match (without $elemMatch)
"views" exists and make those queries much easier to create, with a simple find
db.experiences.find({company:"CompanyA", from: {$gte: ... $lte...}})
Unfortunately when using "views" the engine simply adds the match constraint at the end of the aggregation which turns out to be not always performant see https://support.mongodb.com/case/00767327.
Would it be possible to create an option when creating a view that would be: Create a "view from nestedArray". It is quite simple to create an aggregation query where the query is better optimised because the match is done in the beginning.
/** 1st match if elemMatch */
stages.add(Aggregation.match(Criteria.where(nestedCollection).elemMatch(matchElemCriteria)));
stages.add(Aggregation.unwind(nestedCollection));
/** 2nd match without elemMatch */
stages.add(Aggregation.match(matchCriteria));
stages.add(new GenericAggregationOperation("$project", "{\"" + nestedCollection +"\": \"$" + nestedCollection + "\"}"));
stages.add(new GenericAggregationOperation("$lookup", "{from: \"" + parentCollection + "\",\n" + "localField: \"_id\", foreignField: \"_id\", as: \"" + nestedCollection + "." + patientFieldName + "\"}"));
stages.add(new GenericAggregationOperation("$unwind", "{ path : \"$"+ nestedCollection +"." + patientFieldName + "\"}"));
stages.add(new GenericAggregationOperation("$replaceRoot", "{newRoot: \"$" + nestedCollection + "\"}"));
