Aggregation Guide
- Carl Richter
- Tero Kuitunen (Unlicensed)
- Tobias Kienlein
- Martin Wiegand (Unlicensed)
On this page you will find the most frequently used aggregations.
Content
- 1 Single Numbers (for number fields)
- 1.1 Aggregation with grouping by a selection question
- 1.2 Count Submissions
- 1.3 Count submissions (with a multiple selection question in the DC)
- 1.4 Count of Submissions with more than one option selected in a selection field
- 1.5 Count of unique answers according to a reference question
- 1.6 Sum / average of number field
- 2 Multiple numbers (for charts)
- 2.1 Sum of two number questions
- 2.2 Count submission by selection field
- 2.3 Sum of a number field and group by a selection field
- 2.4 Sum of a multiple number fields and manual grouping
- 2.5 Sum of a number field and automatically group by year
- 2.6 Count of submission grouped by 2 selection questions
- 2.7 Sum of multiple number question values grouped a selection question
- 2.8 Line Chart
- 3 GPS Coordinates
- 4 Sorting of Results
- 4.1 Sort ascending
- 4.2 Sort descending
- 4.3 Sort by 2 fields
- 5 Typical Errors
Single Numbers (for number fields)
Aggregation with grouping by a selection question
If you need to do grouping in aggregations, you have things to consider regarding resolving selection question references. Per default references are resolved over a concrete version. If you do the grouping over just “$answers.country“ in the following example, a country can be multiple times in the result groups when the submissions link them in different versions. This is because the version is included in the grouping criteria.
The correct (was not possible in earlier CONNECT versions) way is to group just by the country code (with explicit _id : {code:"$code"} syntax instead of _id : "$code") and select other required data with the $first operator which has access to the first submission of each group.
Important: with this way the automatic lookup of referenced submissions (here countries) is not working. In the example you have to manually add answers.country to the lookUpPaths so that the referenced submissions are resolved and you can access it’s question data like nameand iso
{
lookUpPaths: [
'answers.country'
],
aggregations: [
{
'$group': {
_id: {
code: '$answers.country.code'
},
name: {
'$first': '$answers.country.answers.name'
},
iso: {
'$first': '$answers.country.answers.iso'
},
count: {
'$sum': 1
}
}
},
{
'$sort': {
count: -1
}
},
{
'$project': {
_id: 0,
name: '$name',
value: '$iso',
result: '$count'
}
}
]
}Count Submissions
Available as aggregation template
Count the number of submissions to a data collection (e.g. number of events, number of partners, …). Each submission (row) is counted as “1”.
If the data collection has the option for multiple selection you must use “Count submissions with a multiple selection question”.
Code:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-01-19T13:02:42.998+00:00',
aggregations: [
{
'$group': {
_id: '',
count: {
'$sum': 1
}
}
},
{
'$project': {
_id: 0,
result: '$count'
}
}
]
}Documentation: https://docs.mongodb.com/manual/reference/operator/aggregation/count/
Count submissions (with a multiple selection question in the DC)
Available as aggregation template
You should use this code if you want to count the submissions to a data callection and the data collection has at least one multiple selection question. Because otherwise when filtering submissions will be counted more than once.
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-03-15T16:18:53.905+00:00',
aggregations: [
{
'$group': {
_id: '$code'
}
},
{
'$count': 'result'
}
]
}Count of Submissions with more than one option selected in a selection field
Count the number of entries within a data collection that have selected more than one option in a selection field.
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-03-11T11:13:23.175+00:00',
aggregations: [
{
'$match': {
'answers.CoE': {
'$exists': true
}
}
},
{
'$addFields': {
coeSize: {
'$size': '$answers.CoE'
}
}
},
{
'$match': {
coeSize: {
'$gte': 2
}
}
},
{
'$count': 'result'
}
]
}Count of unique answers according to a reference question
Count the number of unique answers to a reference question. For example, if you have a list of events that are linked to a country, with this aggregation you can return the number of different countries the events were held.
{
lookUpPaths: [],
modificationTime: '2021-03-23T07:32:51.664+00:00',
aggregations: [
{
'$group': {
_id: '',
countryCodes: {
'$addToSet': '$answers.countryImplementation.code'
}
}
},
{
'$project': {
result: {
'$size': '$countryCodes'
}
}
}
]
}$answers.countryImplementation.code needs to be changed to the path of the needed reference question.
countryCodes needs to be changed according to your context.
Sum / average of number field
Sum / average of all answers to a number question within a data collection (e.g. number of participants).
Code:
Be careful, this code will sum up $answers.beneficiariesTotal if there is a multi selection question which has to be resolved for grouping or filtering.
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-02-23T06:23:01.675+00:00',
aggregations: [
{
'$group': {
_id: '',
result: {
'$sum': '$answers.beneficiariesTotal'
}
}
}
]
}beneficiariesTotal is to be exchanged by the code of the number question.
For average value $sum is to be exchanged with $avg
Documentation:
https://docs.mongodb.com/manual/reference/operator/aggregation/sum/
https://docs.mongodb.com/manual/reference/operator/aggregation/avg/
Multiple numbers (for charts)
Sum of two number questions
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-02-16T16:25:56.849+00:00',
aggregations: [
{
'$group': {
_id: '',
female: {
'$sum': '$answers.female'
},
male: {
'$sum': '$answers.male'
}
}
},
{
'$project': {
_id: 0,
result: [
{
value: 'Female',
result: '$female'
},
{
value: 'Male',
result: '$male'
}
]
}
},
{
'$unwind': '$result'
},
{
'$replaceRoot': {
newRoot: '$result'
}
}
]
}Count submission by selection field
Count the number of entries within a data collection and group them by a selection question (e.g. provinces, gender). Each submission (row) is counted as “1”. This is also possible for number fields.
[
{
value: 'Takhar',
result: 18
},
{
value: 'Samangan',
result: 12
},
{
value: 'Badakhshan',
result: 9
}
]
Code:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-02-23T06:24:49.818+00:00',
aggregations: [
{
'$group': {
_id: '$answers.village.answers.district.answers.province.answers.name',
count: {
'$sum': 1
}
}
},
{
'$sort': {
count: -1
}
},
{
'$project': {
_id: 0,
value: '$_id',
result: '$count'
}
}
]
}$answers.village.answers.district.answers.province.answers.name' is to be exchanged by the path of the selection question.
Sum of a number field and group by a selection field
…
[
{
value: 'Mahārāshtra',
result: 31579
},
{
value: 'West Bengal',
result: 17353
},
{
value: 'Karnātaka',
result: 9942
},
{
value: 'Jhārkhand',
result: 7437
},
{
value: 'Odisha',
result: 2479
},
{
value: 'Delhi',
result: 800
},
{
value: 'Uttar Pradesh',
result: 578
},
{
value: 'Punjab',
result: 578
},
{
value: 'Andhra Pradesh',
result: 491
},
{
value: 'Himāchal Pradesh',
result: 470
},
{
value: 'Uttarākhand',
result: 159
}
]
Code:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-03-29T10:26:23.922+00:00',
aggregations: [
{
'$group': {
_id: '$answers.city.answers.state.answers.state',
count: {
'$sum': '$answers.totalPar'
}
}
},
{
'$sort': {
count: -1
}
},
{
'$project': {
_id: 0,
value: '$_id',
result: '$count'
}
}
]
}Sum of a multiple number fields and manual grouping
[
{
value: 'Adolescents (14-24)',
male: 5,
female: 0
},
{
value: 'Young Adults (25-35)',
male: 61,
female: 2
},
{
value: 'Adults (<35)',
male: 17,
female: 1
}
]
Code:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-02-16T16:25:56.849+00:00',
aggregations: [
{
'$group': {
_id: '',
female14Count: {
'$sum': '$answers.female14'
},
male14Count: {
'$sum': '$answers.male14'
},
female25Count: {
'$sum': '$answers.female25'
},
male25Count: {
'$sum': '$answers.male25'
},
female35Count: {
'$sum': '$answers.female35'
},
male35Count: {
'$sum': '$answers.male35'
}
}
},
{
'$project': {
_id: 0,
result: [
{
value: 'Adolescents (14-24)',
male: '$male14Count',
female: '$female14Count'
},
{
value: 'Young Adults (25-35)',
male: '$male25Count',
female: '$female25Count'
},
{
value: 'Adults (<35)',
male: '$male35Count',
female: '$female35Count'
}
]
}
},
{
'$unwind': '$result'
},
{
'$replaceRoot': {
newRoot: '$result'
}
}
]
}Sum of a number field and automatically group by year
[
{
value: 2020,
result: 535
},
{
value: 2021,
result: 33
}
]
Code:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-03-04T10:26:43.576+00:00',
aggregations: [
{
'$project': {
'answers.dateRecord': {
'$year': '$answers.dateRecord'
},
landDestroyed: '$answers.landDestroyed',
_id: 0
}
},
{
'$group': {
_id: '$answers.dateRecord',
count: {
'$sum': '$landDestroyed'
}
}
},
{
'$sort': {
count: -1
}
},
{
'$project': {
_id: 0,
value: '$_id',
result: '$count'
}
}
]
}Count of submission grouped by 2 selection questions
[
{
'Tendering stage': 4,
'Preparation stage': 2,
'Construction stage': 4,
'Planning stage': 1,
value: 'Ethiopia'
},
{
Completed: 2,
value: 'Togo'
},
{
'Construction stage': 4,
'Tendering stage': 2,
value: 'Kenya'
}
]
{
modificationTime: '2022-01-06T07:55:38.251+00:00',
parameters: [],
parameterValues: {},
filterCriteria: [],
lookUpPaths: [
'answers.country',
'answers.statusCon'
],
aggregations: [
{
'$group': {
_id: {
country: '$answers.country.code',
statusCode: '$answers.statusCon.code'
},
country: {
'$first': '$answers.country.answers.name'
},
status: {
'$first': '$answers.statusCon.answers.name'
},
sum: {
'$sum': 1
}
}
},
{
'$group': {
_id: '$country',
statuses: {
'$push': {
entry: {
k: '$status',
v: '$sum'
}
}
}
}
},
{
'$project': {
country: '$_id',
statuses: {
'$arrayToObject': {
'$map': {
input: '$statuses',
as: 'currentStatus',
in: '$$currentStatus.entry'
}
}
}
}
},
{
'$replaceRoot': {
newRoot: {
'$mergeObjects': [
'$statuses',
{
value: '$country'
}
]
}
}
}
]
}Sum of multiple number question values grouped a selection question
[
{
value: 'Namibia',
Female: 3246,
Male: 4615
},
{
value: 'Senegal',
Female: 0,
Male: 0
},
{
value: 'Togo',
Female: 2300,
Male: 5000
}
]
{
modificationTime: '2022-01-18T07:21:33.544+00:00',
parameters: [],
parameterValues: {},
filterCriteria: [],
lookUpPaths: [
'answers.country'
],
aggregations: [
{
'$group': {
_id: {
country: '$answers.country.code'
},
country: {
'$first': '$answers.country.answers.name'
},
female: {
'$sum': '$answers.fem'
},
male: {
'$sum': '$answers.male'
}
}
},
{
'$project': {
_id: 0,
result: [
{
value: '$country',
Female: '$female',
Male: '$male'
}
]
}
},
{
'$unwind': '$result'
},
{
'$replaceRoot': {
newRoot: '$result'
}
},
{
'$sort': {
value: 1
}
}
]
}Line Chart
[
{
data: [
{
x: 2019,
y: 482
},
{
x: 2020,
y: 380
}
],
id: 'Enrolments'
}
]
{
modificationTime: '2022-01-05T15:05:33.419+00:00',
parameters: [],
parameterValues: {},
filterCriteria: [],
lookUpPaths: [],
aggregations: [
{
'$project': {
_id: 0,
year: {
'$year': {
'$toDate': {
'$toLong': '$answers.year'
}
}
},
studentsTotal: '$answers.studentsTotal'
}
},
{
'$group': {
_id: '$year',
count: {
'$sum': '$studentsTotal'
}
}
},
{
'$sort': {
_id: 1
}
},
{
'$group': {
data: {
'$push': {
x: '$_id',
y: '$count'
}
}
}
},
{
'$project': {
_id: 0,
id: 'Enrolments',
data: 1
}
}
]
}GPS Coordinates
Display coordinates on a map including sum of a number field.
Code:
{
filterCriteria: [],
modificationTime: '2020-04-23T18:01:02.647+00:00',
aggregations: [
{
'$project': {
result: '$answers.householdsTotal',
value: '$answers.name',
geo: '$answers.GPSCoordinate'
}
}
]
}Sorting of Results
Sort ascending
{
$sort: {
maleCount : 1
}
}Sort descending
{
$sort: {
maleCount : -1
}
}Sort by 2 fields
{
$sort: {
"country.name" : 1
maleCount : 1
}
}Documentation: https://docs.mongodb.com/manual/reference/operator/aggregation/sort/
Typical Errors
Name of value is not localised or translated
Problem: en: 'Consultancy'
[
{
value: {
en: 'Consultancy'
},
result: 2
},
{
value: {
en: 'Innovator (thorugh CFI)'
},
result: 1
}
]Fix:
{
filterCriteria: [],
lookUpPaths: [],
modificationTime: '2021-03-04T15:40:05.413+00:00',
aggregations: [
{
'$group': {
_id: '$answers.type.answers.name',
count: {
'$sum': 1
}
}
},
{
'$project': {
_id: 0,
value: '$_id.en',
result: '$count'
}
},
{
'$sort': {
result: -1
}
}
]
}In row 17 you have to ad .en to value: '$_id
Sort nach Sort order
{
parameters: [],
parameterValues: {},
filterCriteria: [],
lookUpPaths: [],
aggregations: [
{
'$group': {
_id: '$answers.statusCon.answers.name',
count: {
'$sum': 1
},
order: {
'$first': '$answers.statusCon.answers.order'
}
}
},
{
'$sort': {
order: 1
}
},
{
'$project': {
_id: 0,
value: '$_id',
result: '$count'
}
}
]
}