CODEWITHSUNDEEP
mongodbaggregation-framework
black_sheep07
black_sheep07

Reputation: 2368

Conditional Count in Subarray Using mongoDB

I have a large dataset that I'd like to use to describe performance of a test. The database is as follows:

db.test.insertMany({
    "_id" : ObjectId("58e574a768afb6085ec3a388"),
    "tests" : [
        {
            "name" : "2",
            "evaluation" : [
                {
                    "aHigh" : [1,2],
                    "aLow" : [ ],
                    "zHigh" : [ ],
                    "zLow" : [1,3]
                },
                {
                    "aHigh" : [1,4],
                    "aLow" : [2],
                    "zHigh" : [ 3],
                    "zLow" : [ ]
                },
                {
                    "aHigh" : [ ],
                    "aLow" : [1,2,3],
                    "zHigh" : [1,2,3,4],
                    "zLow" : [ ]
                },]
            }
            ]
        },
        {
    "_id" : ObjectId("58eba09e51f7f631dd24aa1c"),
    "tests" : [
        {
            "name" : "2",
            "evaluation" : [
                {
                    "aHigh" : [2],
                    "aLow" : [3 ],
                    "zHigh" : [ ],
                    "zLow" : [1,2,3,4]
                },
                {
                    "aHigh" : [ ],
                    "aLow" : [ ],
                    "zHigh" : [ ],
                    "zLow" : [3,4]
                },
                {
                    "aHigh" : [1,2],
                    "aLow" : [3,4],
                    "zHigh" : [ ],
                    "zLow" : [1,2,3,4]
                },]
            }
            ]
        })

I have a set of conditional logic that I need to apply and count the number of outcomes. Essentially, I need to know how many evaluations meet a certain criteria. The logic is:

case1: if the `$size` of the array `aHigh` is `$gte` to 3 AND 
       if the `$size` of the array `aLow` is `$lt` to 1
       then AHI
case2: if the `$size` of the array `aLow` is `$gte` to 3 AND 
       if the `$size` of the array `aHigh` is `$lt` to 1
       then ALO
case3: if the `$sum` of `$size` of the array `aHigh` and the `$size` of the array `aLow` is `$gte` to 3
       then AVR

My expected output is essentially a list. I want something to the effect of

{ "_id" : "AHI", "count" : 5 }
{ "_id" : "ALO", "count" : 15 }
{ "_id" : "AVR", "count" : 8 }
{ "_id" : "ZHI", "count" : 4 }
{ "_id" : "ZLO", "count" : 11 }
{ "_id" : "ZVR", "count" : 10 }

I cannot change the structure of the database. I have been using aggregate to get information. This code does not get me what I want, but it's what I've come up with so far.

db.test.aggregate([
    { $unwind: "$tests" },
    { $unwind: "$tests.evaluation" },
    { $unwind: "$tests.evaluation.aHigh"},
    { $unwind: "$tests.evaluation.aLow"},    
    { $project: {
        "results" : 
    {
        $switch: {
            branches: [
                {
                    case: { $and : [ { $gte : [ { $size : "$tests.evaluation.aHigh" }, 1 ] },
                                { $lt : [ { $size : "$tests.evaluation.aLow" }, 1 ] } 
                                    ] },
                    then: "AHI"
                },
                {
                    case: { $and : [ { $gte : [ { $size : "$tests.evaluation.aLow" }, 1 ] },
                                { $lt : [ { $size : "$tests.evaluation.aHigh" }, 1 ] }] },
                    then: "ALO"
                },
                {
                    case: { $gte : [ {$sum: [ {$size : "$tests.evaluation.aHigh" } , { $size : "$tests.evaluation.aLow" } ] }, 1 ] },
                    then: "AVR"
                }
            ],
            default: ""
        }
    }}}
])

edit:

I'd like to now be able to do the same thing for both the a and for the z.

Upvotes: 1

Views: 248

Answers (1)

s7vr
s7vr

Reputation: 75914

You have one too many $unwind.

Updated your aggregation query to remove extra $unwindstage and added $group to count the results from $project stage.

db.test.aggregate([
    { $unwind: "$tests" },
    { $unwind: "$tests.evaluation" },
    { $project: {
        "results" : {
            $switch: {
                branches: [
                    {
                        case: { $and : [ { $gte : [ { $size : "$tests.evaluation.aHigh" }, 1 ] },
                                    { $lt : [ { $size : "$tests.evaluation.aLow" }, 1 ] } 
                                        ] },
                        then: "AHI"
                    },
                    {
                        case: { $and : [ { $gte : [ { $size : "$tests.evaluation.aLow" }, 1 ] },
                                    { $lt : [ { $size : "$tests.evaluation.aHigh" }, 1 ] }] },
                        then: "ALO"
                    },
                    {
                        case: { $gte : [ {$sum: [ {$size : "$tests.evaluation.aHigh" } , { $size : "$tests.evaluation.aLow" } ] }, 1 ] },
                        then: "AVR"
                    }
                ],
                default: ""
            }
        }}},
    { $group:{_id:"$results", count:{$sum:1}}}  
])

Upvotes: 1

Related Questions