I have the following objects in my MongoDB

[
    {
        'status': 'SENT',
        'country': '_bg',
        'createdAt': '<dataTime>',
    },{
        'status': 'CREATED',
        'country': '_de',
        'createdAt': '<dataTime>',
    },{
        'status': 'SENT',
        'country': '_de',
        'createdAt': '<dataTime>',
    },{
        'status': 'ERROR',
        'country': '_de',
        'createdAt': '<dataTime>',
    },{
        'status': 'SENT',
        'country': '_bg',
        'createdAt': '<dataTime>',
    },
]

and now I'm trying to write an aggregate query to get the desired output

[
    {
        '_de': {
            'SENT': 1,
            'CREATED': 1,
            'ERROR': 1,
            'WAITING': 0
        },
        '_bg': {
            'SENT': 2,
            'CREATED': 0,
            'ERROR': 0,
            'WAITING': 0
        }
    }
]

How to write a query to group by per "countries" and then perform another group by (within the current group by) to count the number of different statuses? Also, if there are no results per status I need to output 0. All available statuses are "SENT", "ERROR", "WAITING", "CANCELED".

Currently I'm working with this query, but it doesn't output wishful results.

const totalPerCounty = await Model.aggregate([
        {
            $match: {
                createdAt: {
                    $gt: new Date(from),
                    $lt: new Date(to),
                }
            },
        },
        {
            $group: {
                _id: {country: '$country'},
                status: {
                    $push: {
                        status: "$status",
                        count: "$count"
                    }
                },
                'count': {$sum: 1}
            }
        }
    ]);

If you need any additional explanations, please let me know and I will provide an answer. Thank you!

CodePudding user response：

1. $match

2. $group - Group by country. And push status into status array.

3. $group - Group by null. This aims to combine all the documents into one by push the $$ROOT document into data array.

4. $replaceRoot - Replace input documents.

   4.1. $arrayToObject - Convert the array into key-value pair.

   4.1.1. $map - Iterate data array element(s) and return a new array with the document contains k as country and v as set of status key-value pair.

   4.1.1.1. $arrayToObject - Convert the array into key-value pair.

   4.1.1.1.1. $map - Iterate the status array and return a new array with the document contains k ass status and v is the count of matching status from data.status array.

db.collection.aggregate([
  {
      $match: {
          createdAt: {
              $gt: new Date(from),
              $lt: new Date(to),
          }
      },
  },
  {
    $group: {
      _id: "$country",
      status: {
        $push: "$status"
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: {
          $map: {
            input: "$data",
            as: "data",
            in: {
              k: "$$data._id",
              v: {
                $arrayToObject: {
                  $map: {
                    input: [
                      "SENT",
                      "ERROR",
                      "WAITING",
                      "CANCELED"
                    ],
                    as: "status",
                    in: {
                      k: "$$status",
                      v: {
                        $size: {
                          $filter: {
                            input: "$$data.status",
                            cond: {
                              $eq: [
                                "$$this",
                                "$$status"
                              ]
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground