CODEWITHSUNDEEP
mongodbaggregation-framework
Albert
Albert

Reputation: 2664

Complex count query in MongoDB

I have a json that have the following structure:

{"data": {
     "key1": "value1",
     "key2": "value2",
     "manualTests": [{"name": "component1", "passed": false, "x": 12}, 
                     {"name": "component2", "passed": true}, 
                     {"name": "component3", "passed": false, "responseTime": 5}],
     "automaticTests": [{"name": "component4", "passed": false}, 
                        {"name": "component5", "passed": true, "conversion": "Z"}, 
                        {"name": "component6", "passed": false}],
     "semiautomaticTests": [{"name": "component7", "passed": true}, 
                            {"name": "component8", "passed": true}, 
                            {"name": "component9", "passed": true}]
}}

My mongoDB contains a really huge number of these and I need to get a list of all the components that have not passed the tests. So the output desired should be:

{
    "component1": 150,
    "component2": 35,
    "component3": 17,
    "component4": 5,
    "component5": 3,
    "component6": 1
}

The numbers are random and for each component they show how many components did pass the tests. How do I calculate it in mongoDB? The format is not strict, the prime requirement is that the output should contain a name of a component failed and their number out of the whole sample.

Upvotes: 0

Views: 108

Answers (1)

s7vr
s7vr

Reputation: 75934

You can try below aggregation query.

$match stage to consider only tests where there is at-least one fail component.

$project with $filter to extract all the failed components followed by $concatArrays to merge all failed components across all tests.

$unwind to flatten the array and $group to count for each failed component.

db.colname.aggregate([
  {"$match":{
    "$or":[
      {"manualTests.passed":false},
      {"automaticTests.passed":false},
      {"semiautomaticTests.passed":false}
    ]
  }},
  {"$project":{
    "tests":{
      "$concatArrays":[
        {"$filter":{"input":"$manualTests","as":"mt","cond":{"$eq":["$$mt.passed",false]}}},
        {"$filter":{"input":"$automaticTests","as":"at","cond":{"$eq":["$$at.passed",false]}}},
        {"$filter":{"input":"$semiautomaticTests","as":"st","cond":{"$eq":["$$st.passed",false]}}}
      ]
    }
  }},
  {"$unwind":"$tests"},
  {"$group":{"_id":"$tests.name","count":{"$sum":1}}}
])

Upvotes: 2

Related Questions