Standard Use Cases of Aggregations

1 Count Submissions
2 Count submissions (with a multiple selection question in the DC)
3 Sum of number field
4 Count submission by selection field
5 Sum of a number field and group by a selection field
6 Sum of a number field and group by 2 selection field.

Count Submissions

{
  filterCriteria: [],
  lookUpPaths: [],
  modificationTime: '2021-01-19T13:02:42.998+00:00',
  aggregations: [
    {
      '$group': {
        _id: '',
        count: {
          '$sum': 1
        }
      }
    },
    {
      '$project': {
        _id: 0,
        result: '$count'
      }
    }
  ]
}

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

You should use this code if you want to count the submissions to a DC and the DC has at least one multiple selection question. Because otherwise when filtering submissions will be counted more than once.

Be carefull when you add filters or grouping which needs lookups to other collection references with multi select enabled. In this case the lookup results to one result per multi selected entry. E. g. if you have a selection of 3 categories, the submission will be 3 times in the result for each category. In this case you have to work with grouping to have unique results to count

{
  filterCriteria: [],
  lookUpPaths: [],
  modificationTime: '2021-03-15T16:18:53.905+00:00',
  aggregations: [
    {
      '$group': {
        _id: '$code'
      }
    },
    {
      '$count': 'result'
    }
  ]
}

Sum of number field

{
  filterCriteria: [],
  lookUpPaths: [],
  modificationTime: '2021-02-23T06:23:01.675+00:00',
  aggregations: [
    {
      '$group': {
        _id: '',
        result: {
          '$sum': '$answers.beneficiariesTotal'
        }
      }
    }
  ]
}

Count submission by selection field

{
  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'
      }
    }
  ]
}

Sum of a number field and group by a selection field

{
  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 number field and group by 2 selection field.

{
  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'
      }
    }
  ]
}

Reframe Knowledge Base