Suppose I have document as userDetails:

[
  {
        "roles": [
            "author",
            "reader"
        ],
        "completed_roles": ["author", "reader"],
        "address": {
            "current_address": {
                "city": "abc"
            }
        },
        "is_verified": true
    },

    {
        "roles": [
            "reader"
        ],
        "completed_roles": ["reader"],
        "address": {
            "current_address": {
                "city": "abc"
            }
        },
        "is_verified": true
    },
    {
        "roles": [
            "author"
        ],
        "completed_roles": [],
        "address": {
            "current_address": {
                "city": "xyz"
            }
        },
        "is_verified": false
    }
]

I want to fetch sum for all roles which has author based on city, total_roles_completed and is_verified.

So the O/P should look like:

[
  {
    "_id": {
      "city": "abc"
    },
    "total_author": 1,
    "total_roles_completed": 1,
    "is_verified": 1
  },
  {
    "_id": {
      "city": "xyz"
    },
    "total_author": 1,
    "total_roles_completed": 0,
    "is_verified": 0
  }
]

Basic O/P required:

• Filter the document based on author in role (other roles may be present in role but author must be present)
• Sum the author based on city
• sum on basis of completed_profile has "author"
• Sum on basis of documents if they are verified.

For this I tried as:

db.userDetails.aggregate([ 
    {
        $match: {
            roles: {
                $eleMatch: {
                    $eq: "author"
                }
            }
        }
    }, 
    {
        $unwind: "$completed_roles"
    },  
    { 
        "$group": { 
            _id: { city: "$address.current_address.city"},             
            total_authors: {$sum: 1},
            total_roles_completed: {
                $sum: {
                    $cond: [
                        {
                            $eq: ["$completed_roles","author"]
                        }
                    ]
                }
            },
            is_verified: {
                $sum: {
                    $cond: [
                        {
                            $eq: ["$is_verified",true]
                        }
                    ]
                }
            }
        }  
    }
]);

But the sum is incorrect. Please let me know where I made mistake. Also, if anyone needs any further information please let me know.

Edit: I figured that because of unwind it is giving me incorrect value, if I remove the unwind the sum is coming correct.

Is there any other way by which I can calculate the sum of total_roles_completed for each city?

CodePudding user response：

If I've understood correctly you can try this query:

• First $match to get only documents where roles contains author.
• And then $group by the city (the document is not a valid JSON so I assume is address:{"current_addres:{city:"abc"}}). This $group get the authors for each city and also: $sum 1 if "author" is in completed_roles and check if is verified.

Here I don't know the way to know if the author is verified (I don't know if can be true in one document and false in other document. If is the same value over all documents you can use $first to get the first is_verified value). But I decided to use $allElementsTrue in a $project stage, so this only will be true if is_verified is true in all documents grouped by $group.

db.collection.aggregate([
  {
    "$match": {
      "roles": "author"
    }
  },
  {
    "$group": {
      "_id": "$address.current_address.city",
      "total_author": {
        "$sum": 1
      },
      "total_roles_completed": {
        "$sum": {
          "$cond": {
            "if": {
              "$in": [
                "author",
                "$completed_roles"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "is_verified": {
        "$addToSet": "$is_verified"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "city": "$_id",
      "is_verified": {
        "$allElementsTrue": "$is_verified"
      },
      "total_author": 1,
      "total_roles_completed": 1
    }
  }
])

Example here

The result from this query is:

[
  {
    "city": "xyz",
    "is_verified": false,
    "total_author": 1,
    "total_roles_completed": 0
  },
  {
    "city": "abc",
    "is_verified": true,
    "total_author": 2,
    "total_roles_completed": 2
  }
]