How do I convert a field from multiple documents into an array in an object with MongoDB?

Question

I have the following documents:

_id: "Team 1"
count: 1200

_id: "Team 2"
count: 1170

_id: "Team 3"
count: 1006

_id: "Team 4"
count: 932

_id: "Team 5"
count: 931

_id: "Team 6"
count: 899

_id: "Team 7"
count: 895

I want to convert the above documents to the following object:

{
    "categories": [
        "Team 2",
        "Team 5",
        "Team 1",
        "Team 7",
        "Team 6",
        "Team 4",
        "Team 3"
    ],
    "series": [
        {
            "counts": [
                1170,
                931,
                1200,
                895,
                899,
                932,
                1006
            ]
        }
    ]
}

The overall order is not important, however, the categories order has to be consistent with the counts order. Meaning if "Team 1" is number 3 in the categories array, its count should also be number 3 in the counts array. How do I achieve this?

I've managed to do it like this:

{
  '$sort': {
    '_id': 1
  }
}, {
  '$group': {
    '_id': null, 
    'categories': {
      '$push': '$_id'
    }, 
    'series': {
      '$push': '$count'
    }
  }
}, {
  '$group': {
    '_id': null, 
    'categories': {
      '$push': '$categories'
    }, 
    'series': {
      '$push': {
        'counts': '$series'
      }
    }
  }
}, {
  '$project': {
    '_id': 0, 
    'categories': 1, 
    'series': 1
  }
}

But I'm pretty sure there is a more clean way of doing it, maybe only using group once?

Answer 1

You can remove the extra group stage and do it in project stage,

• $sort same as you did
• $group same as you did
• $project just correct the series field array construction

[
  { $sort: { _id: -1 } },
  {
    $group: {
      _id: null,
      categories: { $push: "$_id" },
      series: { $push: "$count" }
    }
  },
  {
    $project: {
      _id: 0,
      categories: 1,
      series: [{ counts: "$series" }]
    }
  }
]

Playground