I an new to mongoDB and I am trying to achieve below SQL query equivalent in mongoDB

SELECT ROUND((SELECT COUNT() FROM INFODOCS WHERE ML_PRIORITY = HIGH AND PROCESSOR_ID = userid) / (SELECT COUNT() FROM INFODOCS WHERE PROCESSOR_ID = userid) * 100) AS EFFORTS FROM DUMMY;

EFFORTS = Total High Priority Infodocs / Total Infodocs for a given Processor

I tried to write an aggregation pipeline using $match, $group, $count but the issue is once I get an output for one subquery i did not find anyway how can i compute another subquery and finally use the outputs of both subquery to determine the final result.

CodePudding user response：

The mongo-y way would not to execute two different queries to get the 2 different counts, but to do a sum it dynamically with one query.

You can achieve this in many different ways, here is an example how to use $cond while $grouping to do a conditional sum.

db.collection.aggregate([
  {
    $match: {
      PROCESSOR_ID: "1"
    },
    
  },
  {
    $group: {
      _id: null,
      totalCount: {
        $sum: 1
      },
      priorityHighCount: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$ML_PRIORITY",
                "HIGH"
              ]
            },
            1,
            0
          ]
        }
      }
    }
  },
  {
    $project: {
      EFFORTS: {
        $round: {
          "$multiply": [
            {
              $divide: [
                "$priorityHighCount",
                "$totalCount"
              ]
            },
            100
          ]
        }
      }
    }
  }
])