So this is the sample documents look like

{
    userId: 1,
    totalGames: 10,
    winStats: 4,
    lostStats: 6,
    g1Stats: {
      totalGames: 4,
      winStats: 1,
      lostStats: 3,
    },
    g2Stats: {
      totalGames: 5,
      winStats: 2,
      lostStats: 3,
    },
    g3Stats: {
      totalGames: 1,
      winStats: 1,
      lostStats: 0,
    }    
}

The data returned will be in this format

{
  userId
  totalGames
  winStats 
  lostStats
}

if I get gameType as [g1, g2] then the result will be

{
  userId: 1,
  totalGames: 9,
  winStats: 3,
  lostStats: 6
}

i.e the sum of g1Stats and g2Stats

if the gameType is [] then I have to return

{
 userId: 1,
 totalGames: 10,
 winStats: 4,
 lostStats: 6
}

can someone help me with the query? Note: Also I am performing sort operation on these fields that's why I am using the above format.

CodePudding user response：

Here is one way of doing this:

db.collection.aggregate([
  {
    "$project": {
      userId: 1,
      totalGames: {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$size": {
                  "$concatArrays": [
                    [
                      "g1Stats",
                      "g2Stats"
                    ]
                  ]
                }
              },
              0
            ]
          },
          "then": "$$ROOT.totalGames",
          "else": {
            "$reduce": {
              "input": [
                "g1Stats",
                "g2Stats"
              ],
              "initialValue": 0,
              "in": {
                "$sum": [
                  "$$value",
                  {
                    "$function": {
                      "body": "function(key, doc) {  return doc[key].totalGames }",
                      "args": [
                        "$$this",
                        "$$ROOT"
                      ],
                      "lang": "js"
                    },
                    
                  }
                ]
              }
            }
          }
        }
      },
      winStats: {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$size": {
                  "$concatArrays": [
                    [
                      "g1Stats",
                      "g2Stats"
                    ]
                  ]
                }
              },
              0
            ]
          },
          "then": "$$ROOT.winStats",
          "else": {
            "$reduce": {
              "input": [
                "g1Stats",
                "g2Stats"
              ],
              "initialValue": 0,
              "in": {
                "$sum": [
                  "$$value",
                  {
                    "$function": {
                      "body": "function(key, doc) {  return doc[key].winStats }",
                      "args": [
                        "$$this",
                        "$$ROOT"
                      ],
                      "lang": "js"
                    },
                    
                  }
                ]
              }
            }
          }
        }
      },
      lostStats: {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$size": {
                  "$concatArrays": [
                    [
                      "g1Stats",
                      "g2Stats"
                    ]
                  ]
                }
              },
              0
            ]
          },
          "then": "$$ROOT.lostStats",
          "else": {
            "$reduce": {
              "input": [
                "g1Stats",
                "g2Stats"
              ],
              "initialValue": 0,
              "in": {
                "$sum": [
                  "$$value",
                  {
                    "$function": {
                      "body": "function(key, doc) {  return doc[key].lostStats }",
                      "args": [
                        "$$this",
                        "$$ROOT"
                      ],
                      "lang": "js"
                    },
                    
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

Here is the playground link. Here we use $cond to check if the game type array is empty if it is we pick the value for a key at the top level, otherwise, we use $reduce, $sum, $function to calculate the desired values.