Aggregation to group by multiple fields in a document

Working with Data
,
1

have following data in my collection

{
  “name": “test”,
  "data": {
    “statusOne”: “enabled”,
    “statusTwo”: “active”
  }
}
{
  “name": “test”,
  "data": {
    “statusOne”: “disabled”,
    “statusTwo”: “active”
  }
}
{
  “name": “another-test”,
  "data": {
    “statusOne”: “disabled”,
    “statusTwo”: “active”
  }
}

How to write an aggregation query to display the data like below

“output”: [
{
	“name”: “test”,
	"data": [
		{
			“status”: “active”,
			“count”: 2
		},
		{
			“status”: “disabled”,
			”count”: 1
		},
		{
			“status”: “enabled”,
			”count”: 1
		}
	]
},
{
	“name”: “another-test”,
	"data": [
		{
			“status”: “active”,
			”count”: 1
		},
		{
			“status”: “disabled”,
			”count”: 1
		}
	]
}
]
2

Are those names hard-coded and fixed? If so, you can:

  • Push them into an array
  • Unwind it
  • Group up by name and status then flatten out (optional)
  • Regroup by name, pushing items into array element

Playing about, something like this:

db.getCollection('Test').aggregate([
{
    $addFields:{
         statusAt:[
             '$data.statusOne',
             '$data.statusTwo',
         ]   
    }
},
{
    $unwind:'$statusAt'
},
{
    $group:{
        _id:{
            'name':'$name',
            status:'$statusAt'
        },
        total:{$sum:1}                
    }
},
{
    $project:{
        _id:0,
        'name':'$_id.name',
        status:'$_id.status',
        total:1
    }
},
{
    $group:{
        _id:'$name',
        data:{$push:{'status':'$status', 'count':'$total'}}
    }
}
])

You didn’t say about data volumes etc, so assume with a big dataset you will want to make use of ordering of the stages to take advantage of an index by sorting before you group.

Anyway that’s one option…give you an idea of how it could be done.

You could make use of the attribute pattern for the status data instead to make things a bit more generic and maintainable. Or just have an array of status, your app / model may have special meaning to those names though…

1 Like