Mongodb aggregate query using group-by

Question

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:

I need to group by the attack_type field corresponding to each curl present in the curl_detail.
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)
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)
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.

Ashh · Accepted Answer · 2020-04-11 12:36:08Z

1

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

edited Apr 11, 2020 at 12:36

answered Apr 11, 2020 at 8:34

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ashh Over a year ago

Your output is misleading. See your point in the list if "Pass" is not included then how "attack1" is having "expected" = 3? Same for the "attack2" both should have expected as 1 and 0 respectively.

Kapil Khandelwal Over a year ago

I had added 4th point in the explanation above to make it more clear. how "attack1" is having "expected" = 3? So basically, Curl1 and Curl2 (in document 1) and Curl4 (in document 2) has "flag" : "Attack", , that's why the expected was summed up to 3. Same goes for "attack2" as well. Curl3 (in document 1) and Curl2 (in document 2) has "flag" : "Attack", , hence the expected value was summed to 2 for "attack2" . Let me know if you need more clarification on this. Thanks!!!

Ashh Over a year ago

If "actual" value depends on "curl_result" key then "attack3" should have "actual" = 2? because it has "curl_result" = "pass" = 2 ??

Kapil Khandelwal Over a year ago

Thanks mate!!! It worked like a charm. I don't know if you noticed this or not, but in the final output, I am getting "_id":"attack1" instead of "attack":"attack1"

Collectives™ on Stack Overflow

Mongodb aggregate query using group-by

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related