I am trying to solve a problem. I want to write a query that finds a document among my documents which one is greater by the sum of columns A and B's in an array. I write an example down here. I am new to MongoDB and I've been searching a lot but I could not find my solution. So can somebody help me to solve this problem? Here are my sample documents:

document1:

 {
    "_id" : "1",
        "array": [
    {
        "user": "1",
        "A": 2,
        "B": 0
    },
    {
        "user": "2",
        "A": 3,
        "B": 1},
    {
        "user": "3",
        "A": 0,
        "B": 5
    }
                 ]
 }

and document 2:

 {
    "_id" : "2",
        "array": [
    {
        "user": "4",
        "A": 1,
        "B": 1
    },
    {
        "user": "5",
        "A": 2,
        "B": 2
    }
                 ]
 }

for example, the sum of A and B's in all elements of an array in document 1 is 11 and the sum of A and B's in elements of an array in document 2 is 6. So I want to get document 1 for output because it is greater than 2 after summing all A and B's in all of the elements.

CodePudding user response：

You can try this query:

• Create an auxiliar field called total (or whatever name you want) and $add values. This add the $sum of the arrays. That means here you are adding all values from A and B together.
• Then sort by the auxiliar field to get the greatest at first position
• $limit to only one (the greatest)
• And $project to not output the auxiliar field.

db.collection.aggregate([
  {
    "$addFields": {
      "total": {
        "$add": [
          {
            "$sum": "$array.A"
          },
          {
            "$sum": "$array.B"
          }
        ]
      }
    }
  },
  {
    "$sort": {
      "total": -1
    }
  },
  {
    "$limit": 1
  },
  {
    "$project": {
      "total": 0
    }
  }
])

Example here