view on nested array
- 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 + "\"}"));
![](https://secure.gravatar.com/avatar/dfc11e9683e08144fe8709098e1a1811?size=40&default=https%3A%2F%2Fassets.uvcdn.com%2Fpkg%2Fadmin%2Ficons%2Fuser_70-6bcf9e08938533adb9bac95c3e487cb2a6d4a32f890ca6fdc82e3072e0ea0368.png)