Query only for numbers in nested array

Question

I am trying to get an average number of an key in a nested array inside a document, but not sure how to accomplish this.

Here is how my document looks like:

{
  "_id": {
    "$oid": "XXXXXXXXXXXXXXXXX"
  },
  "data": {
    "type": "PlayerRoundData",
    "playerId": "XXXXXXXXXXXXX",
    "groupId": "XXXXXXXXXXXXXX",
    "holeScores": [
      {
        "type": "RoundHoleData",
        "points": 2
      },
      {
        "type": "RoundHoleData",
        "points": 13
      },
      {
        "type": "RoundHoleData",
        "points": 3
      },
      {
        "type": "RoundHoleData",
        "points": 1
      },
      {
        "type": "RoundHoleData",
        "points": 21
      }
    ]
  }
}

Now, the tricky part of this is that I only want the average of points for holeScores[0] of all documents with this playerid and this groupid.

Actually, the best solution would be collecting all documents with playerid and groupid and create a new array with the average of holeScores[0], holeScores[1], holeScores[2]... But if I only can get one array key at the time, that would be OK to :-)

Here is what I am thinking but not quit sure how to put it together:

var allScores = dbCollection('scores').aggregate(
    {$match: {"data.groupId": groupId, "playerId": playerId}},
    {$group: {
        _id: playerId, 
        rounds: { $sum: 1 }
        result: { $sum: "$data.scoreTotals.points" }
    }}
);

Really hoping for help with this issue and thanks in advance :-)

mickl · Accepted Answer · 2018-10-01 07:23:33Z

1

You can use $unwind with includeArrayIndex to get index and then use $group to group by that index

dbCollection('scores').aggregate(
    {
        $match: { "data.playerId": "XXXXXXXXXXXXX", "data.groupId": "XXXXXXXXXXXXXX" }
    },
    {
        $unwind: {
            path: "$data.holeScores",
            includeArrayIndex: "index"
        }
    },
    {
        $group: {
            _id: "$index",
            playerId: { $first: "data.playerId" }, 
            avg: { $avg: "$data.holeScores.points" }
        }
    }
)

edited Oct 1, 2018 at 7:23

answered Oct 1, 2018 at 5:43

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mansa Over a year ago

It comes up with this error: Function aggregate does not accept parameters (object).? Any idea?

Mansa Over a year ago

Perfect, it does the job. Have one question though. Is it possible to also get the playerid in the output of this before the array of averages?

mickl Over a year ago

@Mansa sure, modified my answer

Ashh · Accepted Answer · 2018-10-01 06:51:54Z

1

You can try below aggregation

db.collection.aggregate(
  { "$match": { "data.groupId": groupId, "data.playerId": playerId }},
  { "$group": {
    "_id": null,
    "result": {
      "$sum": {
        "$arrayElemAt": [
          "$data.holeScores.points",
          0
        ]
      }
    }
  }}
)

edited Oct 1, 2018 at 6:51

answered Oct 1, 2018 at 5:43

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

1 Comment

Mansa Over a year ago

It comes up with this error: Function aggregate does not accept parameters (object).? Any idea?

Collectives™ on Stack Overflow

Query only for numbers in nested array

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related