On this page you will find the most frequently used aggregations.

Content

1 Single Numbers (for number fields)
2 Multiple numbers (for charts)
3 GPS Coordinates
4 Sorting of Results
- 4.1 Sort ascending
- 4.2 Sort descending
- 4.3 Sort by 2 fields
5 Typical Errors
- 5.1 Name of value is not localised or translated
- 5.2 Sort nach Sort order

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.

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.

$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.

beneficiariesTotal is to be exchanged by the code of the number question.

For average value $sum is to be exchanged with $avg

Documentation:

Multiple numbers (for charts)

Sum of two number questions

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.

Code:

$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

…

Code:

Sum of a multiple number fields and manual grouping

Code:

Sum of a number field and automatically group by year

Code:

Count of submission grouped by 2 selection questions

Sum of multiple number question values grouped a selection question

Line Chart

GPS Coordinates

Display coordinates on a map including sum of a number field.

Code:

Sorting of Results

Sort ascending

Sort descending

Sort by 2 fields

Documentation: https://docs.mongodb.com/manual/reference/operator/aggregation/sort/

Typical Errors

Name of value is not localised or translated

Problem: en: 'Consultancy'

Fix:

In row 17 you have to ad .en to value: '$_id

Reframe Knowledge Base

Aggregation Guide

Single Numbers (for number fields)

Aggregation with grouping by a selection question

Count Submissions

Count submissions (with a multiple selection question in the DC)

Count of Submissions with more than one option selected in a selection field

Count of unique answers according to a reference question

Sum / average of number field

Multiple numbers (for charts)

Sum of two number questions

Count submission by selection field

Sum of a number field and group by a selection field

Sum of a multiple number fields and manual grouping

Sum of a number field and automatically group by year

Count of submission grouped by 2 selection questions

Sum of multiple number question values grouped a selection question

Line Chart

GPS Coordinates

Sorting of Results

Sort ascending

Sort descending

Sort by 2 fields

Typical Errors

Name of value is not localised or translated

Sort nach Sort order