Aggregation query assistance

Working with Data
1

Can you please advice how can i filter the ratio and the cases columns?

I want to filter ratio>0.5 and cases>2

Basically I’m trying to convert below tsql query

select
case when cast(rejects as float)/ cast(cases as float) > 0.66 and cases >=20 then ‘1’ else ‘0’ end as Alert from
( select count (distinct uuid) cases, count (distinct case when [meta.ui.step] =
‘RejectedMBCheck’ or [meta.ui.step] = ‘rejectedBCheck’ or [meta.ui.step]
= ‘rejectedWrongDetails’ then [Uuid]end ) rejects, cast([createdDate.$date] as date)
date from dwh_fact_cases group by cast([createdDate.$date] as date)

Here is my query:

var lastHour = new Date();
lastHour.setHours(lastHour.getHours()-24);
    
db.tt.aggregate([    
    { $match: { "lastModifiedDate":{$gt: lastHour} } },
    { $group: {          
        _id: {              
            month: { $month: "$lastModifiedDate" },             
            day: { $dayOfMonth: "$lastModifiedDate" },             
            year: { $year: "$lastModifiedDate" },                       
        },         
        reject: { $sum: { $cond : [{ $in : ["$meta.ui.step", ["rejectedCheck","rejectedWrongDetails"]]}, 1, 0]} },
        cases: { $sum: 1 }
    } }, 
    {$project:{Aborted:1, reject:1, cases:1, ratio: { $divide: [ "$reject", "$cases" ]}}
    },
 
   ])

Here is a sample document:

{
    "_id" : ObjectId("5f058e3feab5bf000668563f"),
     "meta" : {
        "ui" : {
            "step" : "rejectedCheck",
            "wasStepCompleted" : true,
            "uiVersion" : "5.5.2"
        },
        "attributions" : []
    },
    "custom" : {
        "CaseId" : "2070r41111",
        "statusLastUpdate" : NumberLong(1594206118668)
    },
    "caseToken" : "hjkhjkhj.tyjggy",
    "uuid" : "bd5c0b13-fe0d-4d94-a1b3-aeb66b1ad61d",
    "createdDate" : ISODate("2020-07-08T09:13:35.699Z"),
    "lastModifiedDate" : ISODate("2020-07-08T11:01:58.717Z")
  
}
2

Hello, @Lital_ez!

In order to help you, provide example of a document from your collection.
Also, please, make sure your post is well-formatted and easily readable.

3

hey.
Do you need more info?

Thanks

4

Hi, @Lital_ez!

Ok, so the example output of your aggregation is:

{
  "_id" : {
    "month" : 7,
    "day" : 8,
    "year" : 2020
  },
  "reject" : 1,
  "cases" : 1,
  "ratio" : 1
}

You want to filter this out that result by those conditions, right?

If so, you can just add another $match stage in the end of your pipeline:

db.tt.aggregate([
  { $match: { /* ... */ } },
  { $group: { /* ... */ } },
  { $project: { /* ... */ } },
  {
    $match: {
      ratio: {
        $gt: 0.5
      },
      cases: {
        $gt: 2
      }
    }
  }
]);
5

Thanks it working…can you also advice why below returning same value?
both should return different results

reject: { $sum: { $cond : [{ $in : ["$meta.ui.step", [“rejectedMBCheck”,“rejectedBBLCheck”,“rejectedWrongDetails”]]}, 1, 1]} },

cases: { $sum: 1 }

6 
[
  /* ... */
  {
    $group: {
      /* ... */
      reject: {
        $sum: {
          $cond: [
            {
              $in : [
                "$meta.ui.step", 
                [
                  'rejectedMBCheck',
                  'rejectedBBLCheck',
                  'rejectedWrongDetails'
                ]
              ]
            },
            1, // returns, if above contition will resolve to TRUE
            1, // returns, if above contition will resolve to FALSE
          ]
        }
      },
    }
  },
  /* ... */
]

Have a look at your condition inside $cond operator: no matter to what value (true or false) your condition is resolved, you will always take the same action - add 1.
So, it works just like:

{ $sum: 1 }

That’s why you get the same result for both expressions.

7

Thanks for you fast reply!!!
so its should be 1,0

$group: {
      /* ... */
      reject: {
        $sum: {
          $cond: [
            {
              $in : [
                "$meta.ui.step", 
                [
                  'rejectedMBCheck',
                  'rejectedBBLCheck',
                  'rejectedWrongDetails'
                ]
              ]
            },
            1, // returns, if above contition will resolve to TRUE
            0, // returns, if above contition will resolve to FALSE
          ]
        }
      },
    }
  },
  /* ... */
]
8

Yes, if you want to count only values, that would resolve to true with your $cond operator :slight_smile:

1 Like
9

its not working. can you please advise?
reject: { $sum: { $cond : [{ $in : ["$meta.ui.step", [“rejectedMBCheck”]]}, 1,0]}},

10

Can you provide a small dataset and the aggregation you use, so I can reproduce the issue?

11

while preparing smaple data for you it did work.
so all good
thanks a lot!!! :dizzy:

12

hey slava,
i think there is a bug in the aggregation framework

working

db.getCollection('test').find(

{$and: [      {'lastModifiedDate': {$gte: ISODate('2019-07-13T00:00:00.000Z')}},{'lastModifiedDate': {$lte: ISODate('2020-07-29T23:59:59.999Z')}}
 ,{'meta.ui.step':{$in:["brokerInitiated","rejectedMBCheck","rejectedBBLCheck","rejectedWrongDetails"]}}
      ]       
   }
   )

not working

var lastHour = new Date();
lastHour.setHours(lastHour.getHours()-24000);
  lastHour
db.test.aggregate([    
    { $match: { "lastModifiedDate":{$gt: lastHour} } },
    { $group: {          
        _id: {              
            month: { $month: "$lastModifiedDate" },             
            day: { $dayOfMonth: "$lastModifiedDate" },             
            year: { $year: "$lastModifiedDate" },                       
        },         
        reject: { $sum: { $cond : [{ $in : ["$meta.ui.step", ["brokerInitiated","rejectedBBLCheck","rejectedWrongDetails"]]}, 1,0]}},
            cases: { $sum: 1 }
    } }, 
    {$project:{Aborted:1, reject:1, cases:1, ratio: { $divide: [ "$reject", "$cases" ]}}
    },
     {    $match: { ratio: {  $gt: 0.5}, cases: {$gt: 1 } }}
])