CODEWITHSUNDEEP
mongodbgroup-bymongodb-queryaggregation-frameworkgrouping
Kapil Khandelwal
Kapil Khandelwal

Reputation: 1176

Mongodb aggregate query using group-by

My MongoDB stores documents having the following structure:

{
    "curl_detail" : {
        "Curl1" : {
            "attack_type" : "attack1",          
            "flag" : "Attack",
            "curl_result" : "Pass"
        },
        "Curl2" : {
            "attack_type" : "attack1",
            "flag" : "Attack",
            "curl_result" : "Pass"
        },
        "Curl3" : {
            "attack_type" : "attack2",
            "flag" : "Attack",
            "curl_result" : "Pass"
        },
        "Curl4" : {
            "attack_type" : "attack3",
            "flag" : "Attack",
            "curl_result" : "Fail"
        }
}
{
    "curl_detail" : {
        "Curl1" : {
            "attack_type" : "attack3",
            "flag" : "Attack",
            "curl_result" : "Pass"
        },
        "Curl2" : {
            "attack_type" : "attack2",
            "flag" : "Attack",
            "curl_result" : "Pass"
        },
        "Curl3" : {
            "attack_type" : "attack3",
            "flag" : "Pass",
            "curl_result" : "Pass"
        },
        "Curl4" : {
            "attack_type" : "attack1",
            "flag" : "Attack",
            "curl_result" : "Fail"
        }
}

How can I perform MongoDB aggregation to achieve the following output:

[{
    attack: "attack1",
    expected: 3,
    actual: 2
},
{
    attack:"attack2",
    expected: 2,
    actual: 2
},
{
    attack: "attack3",
    expected: 2,
    actual: 2
}]

Explanation for output required:

  1. I need to group by the attack_type field corresponding to each curl present in the curl_detail.
  2. Now the expected field in the output is the sum of "flag" key having value as "Attack" (Note that "Pass" values are not included in the sum. See the expected value attack3 in the output for more clarification)
  3. And the actual field in the output is the sum of "curl_result" key having value as "Pass". (Note that "Fail" values are not included in the sum)
  4. The sum calculated for expected value of output depends only on "flag" key and the sum calculated for actual value of output depends only on the "curl_result" key.

Upvotes: 1

Views: 89

Answers (1)

Ashh
Ashh

Reputation: 46491

You can use below aggregation

db.collection.aggregate([
  { "$addFields": {
    "curl_detail": {
      "$objectToArray": "$curl_detail"
    }
  }},
  { "$unwind": "$curl_detail" },
  { "$group": {
    "_id": "$curl_detail.v.attack_type",
    "expected": {
      "$sum": {
        "$cond": [
          { "$eq": ["$curl_detail.v.flag", "Attack"] },
          1,
          0
        ]
      }
    },
    "actual": {
      "$sum": {
        "$cond": [
          { "$eq": ["$curl_detail.v.curl_result", "Pass"] },
          1,
          0
        ]
      }
    }
  }},
  { "$addFields": { "attack": "$_id", "_id": "$$REMOVE" }}
])

MongoPlayground

Upvotes: 1

Related Questions