Group by context and get the sum for lesser than 24h and greater than 24h-CodePudding

I want to get the result as

{
  context: 'ABC',
  lesser_12h: 32,
  lesser_24: 64
},

{
  context: 'DEF',
  lesser_12h: 12,
  lesser_24: 93
}

And for that, I've written this query. But I'm having difficulty in summing the results. It just gives me 0 or 1, in the lesser_ variables.

How to get the sum/count?

const aggregation = [
  { $match: { EVENTTIME: { $gte: startDate } } },
  { 
    $group: {
      _id: {
        tech: '$CONTEXT',
        lesser_12h: {
          $sum: {
            $cond: [
              { 
                $lte: [{ $subtract: [ '$EVENTTIME', startDate ] }, 43200000]
              },
              1,
              0
            ]
          }
        },
        lesser_24h: { 
          $sum: {
            $cond: [
              { 
                $lte: [{ $subtract: [ '$EVENTTIME', startDate ] }, 86400000]
              },
              1,
              0
            ]
          }
        }
      }
    }
  }
];

CodePudding user response：

As discussed in the comment, the lesser_12h and lesser_24h fields became part of the group key. The $group stage should be:

const aggregation = [
    { $match: { EVENTTIME: { $gte: startDate } } },
    { 
      $group: {
        _id: {
          tech: '$CONTEXT',
        },
        lesser_12h: {
          $sum: {
            $cond: [
              { 
                $lte: [{ $subtract: [ '$EVENTTIME', startDate ] }, 43200000]
              },
              1,
              0
            ]
          }
        },
        lesser_24h: { 
          $sum: {
            $cond: [
              { 
                $lte: [{ $subtract: [ '$EVENTTIME', startDate ] }, 86400000]
              },
              1,
              0
            ]
          }
        }
    }
];

And the $group key:

_id: {
    tech: '$CONTEXT',
}

can be replaced with

_id: '$CONTEXT'

as you just group with single field. And in further stages, you access with $_id rather than $_id.tech.