How to count occurrences of records in nested array field-CodePudding

Here is my data

[
  { // room data
    _id: '20ae0225-512a-405b-8b9f-d6ffdca6634c',
    games: [
      {
        _id: 'cb01da11-5809-43e6-b02e-e878b38f4e11',
        players: [
          {
            user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07',
            user_name: 'Andrew'
          },
          {
            user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7',
            user_name: 'Jack'
          },
          
        ]
      },
      {
        _id: '9b03d0ef-178e-49d1-8b86-6b63c1957d6f',
        players: [
          {
            user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07',
            user_name: 'Andrew'
          },
          {
            user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95',
            user_name: 'Thomas'
          }
        ]
      },
      {
        _id: '733a30d5-a6c1-4ce2-a33b-c8dbf61f2a2e',
        players: [
          {
            user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7',
            user_name: 'Jack'
          },
          {
            user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95',
            user_name: 'Thomas'
          }
        ]
      }
    ]
  },
  { // room data
    _id: '20ae0225-512a-405b-8b9f-d6ffdca6634c',
    games: [
      {
        _id: 'cb01da11-5809-43e6-b02e-e878b38f4e11',
        players: [
          {
            user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95',
            user_name: 'Thomas'
          },
          {
            user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7',
            user_name: 'Jack'
          },
          
        ]
      },
      {
        _id: '9b03d0ef-178e-49d1-8b86-6b63c1957d6f',
        players: [
          {
            user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07',
            user_name: 'Andrew'
          },
          {
            user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95',
            user_name: 'Thomas'
          }
        ]
      },
      {
        _id: '733a30d5-a6c1-4ce2-a33b-c8dbf61f2a2e',
        players: [
          {
            user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7',
            user_name: 'Jack'
          },
          {
            user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95',
            user_name: 'Thomas'
          }
        ]
      }
    ]
  }
]

i want to count the total number of occurrences of a player appearing in the game array by user_name or _id Example: In first room, player Andrew joined 2 game. In second room, player Andrew joined 1 game. The result of the desired query is 3.

Please help me, I can't think of a suitable query for this problem. I don't use Mongoose, I use pure query.

Thank everyone.

CodePudding user response：

In case where your data is an array called data try

const arr = []


data.forEach(el => {
  el.games.forEach(game => {
    game.players.forEach(player => {
      const playersNames = arr.map(playerObj => playerObj?.name)
      if (!playersNames.includes(player.user_name)) {
        arr.push({
          name: player.user_name,
          count: 0
        })
      }
      const wantedPlayer = arr.find(e => e.name === player.user_name);
      wantedPlayer.count  
    })
  })
})

console.log(arr)

it could be refactored to use an object map instead of an array but that should work for you

EDIT: here the refactored code to use an object

const objMap = {}

data.forEach(el => {
  el.games.forEach(game => {
    game.players.forEach(player => {
      if (!objMap[player.user_name]) {
        objMap[player.user_name] = {
          name: player.user_name,
          count: 0
        }
      }
      objMap[player.user_name].count  
    })
  })
})

console.log(objMap)

CodePudding user response：

You can use aggregation operations.

db.collection.aggregate({
  $unwind: "$games"
},
{
  $unwind: "$games.players"
},
{
  $group: {
    _id: "$games.players.user_name",
    count: {
      $sum: 1
    }
  }
})

https://mongoplayground.net/p/5nD_vlz0CEb