How to get counts from multiple field keys and values with simple result?

Working with Data
, ,
1 
db.case_details.aggregate( [
  {
    $facet: {
      "Totalcount":[{$count:"count"},{$project:{_id:0,Total_cases:"$count"}}],
      "status_counts": [{"$group" : {_id:"$status", count:{$sum:1}}},{$project:{_id:0,count:1,status:"$_id"}}],
      "Priority_counts": [{$match:{"status":{$ne:"Closed"}}},{"$group" : {_id:"$priority", count:{$sum:1}}},{$project:{_id:0,priority:"$_id",count:1}}] ,
      "assigned":[{$match:{"status":{$ne:"Closed"}}},{"$group":{_id:"$is_assigned",count:{$sum:1}}},{$project:{_id:0,assigned:"$_id",count:1}}],
      "transfered":[{$match:{"status":{$ne:"Closed"},"is_transfer":true}},{"$group":{_id:"$is_transfer",count:{$sum:1}}},{$project:{_id:0,transfered:"$_id",count:1}}],
      "cc":[{$match:{"status":{$ne:"Closed"},"iscccase":true}},{"$group":{_id:"$iscccase",count:{$sum:1}}},{$project:{_id:0,cccase:"$_id",count:1}}]      
          }
        },{$addFields : {created_date : new Date()}}
      ])

from the above query I got result as like below:

/* 1 */
{
    "Totalcount" : [ 
        {
            "Total_cases" : 64397
        }
    ],
    "status_counts" : [ 
        {
            "count" : 696.0,
            "status" : "Open"
        }, 
        {
            "count" : 59662.0,
            "status" : "Closed"
        }, 
        {
            "count" : 4039.0,
            "status" : "Pending"
        }
    ],
    "Priority_counts" : [ 
        {
            "count" : 2.0,
            "priority" : "High"
        }, 
        {
            "count" : 4722.0,
            "priority" : "Escalated"
        }, 
        {
            "count" : 11.0,
            "priority" : "Medium"
        }
    ],
    "assigned" : [ 
        {
            "count" : 4351.0,
            "assigned" : true
        }, 
        {
            "count" : 384.0,
            "assigned" : false
        }
    ],
    "transfered" : [ 
        {
            "count" : 245.0,
            "transfered" : true
        }
    ],
    "cc" : [ 
        {
            "count" : 4.0,
            "cccase" : true
        }
    ],
    "created_date" : ISODate("2022-09-07T07:29:07.735Z")
}

can we get result like:

{Total:57875, Open:696, Pending:4039 , Closed:59662, Low:11, Escalated:4722, Medium:1, High:1, Assigned: 4351, Unassigned : 384, Transfered: 245, cc :4}

2

Hi @Lokesh_Reddy1 ,

Its just a matter of playing with the projection, not sure if the complication of the query worth it :slight_smile:

db.case_details.aggregate( [
  {
    $facet: {
      "Totalcount":[{$count:"count"},{$project:{_id:0,Total_cases:"$count"}}],
      "status_counts": [{"$group" : {_id:"$status", count:{$sum:1}}},{$project:{_id:0,count:1,status:"$_id"}}],
      "Priority_counts": [{$match:{"status":{$ne:"Closed"}}},{"$group" : {_id:"$priority", count:{$sum:1}}},{$project:{_id:0,priority:"$_id",count:1}}] ,
      "assigned":[{$match:{"status":{$ne:"Closed"}}},{"$group":{_id:"$is_assigned",count:{$sum:1}}},{$project:{_id:0,assigned:"$_id",count:1}}],
      "transfered":[{$match:{"status":{$ne:"Closed"},"is_transfer":true}},{"$group":{_id:"$is_transfer",count:{$sum:1}}},{$project:{_id:0,transfered:"$_id",count:1}}],
      "cc":[{$match:{"status":{$ne:"Closed"},"iscccase":true}},{"$group":{_id:"$iscccase",count:{$sum:1}}},{$project:{_id:0,cccase:"$_id",count:1}}]      
          }
        },{$addFields : {created_date : new Date()}},{
 $project: {
  Total: {
   $first: '$Totalcount.Total_cases'
  },
  Open: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$status_counts',
       cond: {
        $eq: [
         '$$this.status',
         'Open'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Pending: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$status_counts',
       cond: {
        $eq: [
         '$$this.status',
         'Pending'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Closed: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$status_counts',
       cond: {
        $eq: [
         '$$this.status',
         'Closed'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Low: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$Priority_counts',
       cond: {
        $eq: [
         '$$this.priority',
         'Low'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Escalated: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$Priority_counts',
       cond: {
        $eq: [
         '$$this.priority',
         'Escalated'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Medium: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$Priority_counts',
       cond: {
        $eq: [
         '$$this.priority',
         'Medium'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  High: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$Priority_counts',
       cond: {
        $eq: [
         '$$this.priority',
         'High'
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Assigned: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$assigned',
       cond: {
        $eq: [
         '$$this.assigned',
         true
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Unassigned: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$assigned',
       cond: {
        $eq: [
         '$$this.assigned',
         false
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  Transfered: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$transfered',
       cond: {
        $eq: [
         '$$this.transfered',
         true
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  },
  cc: {
   $ifNull: [
    {
     $first: {
      $filter: {
       input: '$cc',
       cond: {
        $eq: [
         '$$this.cccase',
         true
        ]
       }
      }
     }
    },
    {
     count: 0
    }
   ]
  }
 }
}, {
 $project: {
  Total: 1,
  Open: '$Open.count',
  Pending: '$Pending.count',
  Closed: '$Closed.count',
  Low: '$Low.count',
  Escalated: '$Escalated.count',
  Medium: '$Medium.count',
  High: '$High.count',
  Assigned: '$Assigned.count',
  Unassigned: '$Unassigned.count',
  Transfered: '$Transfered.count',
  cc: '$cc.count'
 }
}]);

:slight_smile:

1 Like
3

Hi @Pavel_Duchovny I am very excited for your response its working fine… Thank so much :hugs:

4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.