Conditional Count in Subarray Using mongoDB

Question

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.

s7vr · Accepted Answer · 2017-04-24 00:17:12Z

1

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}}}  
])

edited Apr 24, 2017 at 0:17

answered Apr 24, 2017 at 0:03

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

8 Comments

black_sheep07 Over a year ago

Side question - the result from the default case gives me { "_id" : "", "count" : 1 }. Is there any better way to choose this value as non-existent?

s7vr Over a year ago

default stage is optional but it throws error when none of the cases evaluates to true. I think you just need to add a $match stage after $project stage to filter empty value _ids. Something like {$match:{_id:{$ne:""}}}

black_sheep07 Over a year ago

If you're ok with it, I'd like to add more complexity to my question above.

s7vr Over a year ago

Sure if you think it is not complex enough for separate question.

s7vr Over a year ago

Okay I looked at the edit. Isn`t that just adding more cases to the switch statement ?

|

Collectives™ on Stack Overflow

Conditional Count in Subarray Using mongoDB

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related