Aggregate data to group by first value of an array

Question

Assume there is the following data in my db and I want to aggregate some data grouped by the first target score value.

{
    "_id" : ObjectId("5d7f6a937563a63c1d8b4639"),
    "target" : [
        {
            "score" : 3
        },
    {
            "score" : 2
        }
    ]
},
{
    "_id" : ObjectId("5d7f6a937563a63c1d8b4640"),
    "target" : [
        {
            "score" : 1
        },
    {
            "score" : 4
        }
    ]
}

So I'm trying to do this:

data.aggregate(
  [
    {
      $match: { 'target.0.score': { $exists: true } }
    },
    {
      $group: {
        _id: '$target.0.score',
        Datasets: { $sum: 1 }
      }
    }
  ]
)

In my code I'm doing some average calculation, that's why I'm using the aggregate method

The result of the query is

[ { _id: [], Datasets: 2 } ]

But I would expect

[
  { _id: 3, Datasets: 1 },
  { _id: 1, Datasets: 1 }
]

_id should be the grouped score value with the count of all datasets of this score (and some average calculation for this group)

It's a find/match syntax to access elements in array. For $group you need to use $arrayElemAt. see https://stackoverflow.com/questions/39196537/project-first-item-in-an-array-to-new-field-mongodb-aggregation — Alex Blex, Oct 30 '19 at 18:09
@AlexBlex But `$arrayElemAt: ['$target', 0]` gives me the object. I need to get the score value. — user3142695, Oct 30 '19 at 18:19
@AlexBlex Why did you delete your answer. It was working. Lucas' answer does not consider the `score` field. — user3142695, Oct 30 '19 at 18:32
Fair enough. I undeleted mine to show an alternative approach and corrected Lucas' one to use `score`. The extra stage is better in this particular case. $addFields instead of $project might be a bit more flexible tho. — Alex Blex, Oct 31 '19 at 09:17

score 1 · Answer 1 · answered Oct 30 '19 at 18:25

I'm pretty sure it was already answered, but anyway:

data.aggregate(
  [
    {
      $match: { 'target.0.score': { $exists: true } }
    },
    {
      $group: {
        _id: {$let:{
            vars: {t0: {$arrayElemAt:["$target", 0]}},
            in: "$$t0.score"
        }},
        Datasets: { $sum: 1 }
      }
    }
  ]
)

score 0 · Accepted Answer · edited Oct 31 '19 at 09:13

If you want to group by first target score, first you need to project the first target score, then group by it:

data.aggregate(
  [
    {
      $match: { 'target.0.score': { $exists: true } }
    },
    {
      $project: {
        first_target_score: {
          $arrayElemAt: [
            '$target', 0
          ]
        }
      }
    },
    {
      $group: {
        _id: '$first_target_score.score',
        Datasets: { $sum: 1 }
      }
    }
  ]
)

I'm pretty sure this will work, but I don't have the means to test it out now, just give it a try

Aggregate data to group by first value of an array

2 Answers2