Mongo Project into an aggregate object

user_5399779 0
Viewed 45

my SQL brain is a little new to the mongo query language and I'm having issues forming what should be a simple query.

I have 4 objects in a collection and I would like to form an aggregation that simply reports the number of open and closed objects where the Requestor.Info.Id == 155

{Open: 2, Closed: 1}

I've started with an aggregation like this, however, I'm not sure how to sum it into 2 fields like above.

db.getCollection("sample").aggregate(
[
    {
        $match: {
            "Requestor.Info._id": "155"
        }
    },
    {
        $project: {
            Open: {$sum: {$eq: {"JobStatus.Status": 0}},
            Closed: {$sum: {$eq: {"JobStatus.Status": 1}}
        }
    }
]

here is a cutdown version of the 4 documents

{
"_id" : 1,
"Requestor" : {
    "Info" : {
        "_id" : "155",
        "Name" : "James"
    },
},
"JobStatus" : {
    "_id" : {
        "$binary" : "RtegzGJrakmEWnS26ffviQ==",
        "$type" : "03"
    },
    "JobId" : 1,
    "Status" : 0,
    "Created" : {
        "$date" : 1631895760470
    }
},
"Created" : {
    "$date" : 1631890789396
}
},
{
"_id" : 2,
"Requestor" : {
    "Info" : {
        "_id" : "155",
        "Name" : "Fred"
    },
},
"JobStatus" : {
    "_id" : {
        "$binary" : "RtegzGJrakmEWnS26ffviQ==",
        "$type" : "03"
    },
    "JobId" : 2,
    "Status" : 0,
    "Created" : {
        "$date" : 1631895760470
    }
},
"Created" : {
    "$date" : 1631890789396
}
},
{
"_id" : 3,
"Requestor" : {
    "Info" : {
        "_id" : "155",
        "Name" : "John"
    },
},
"JobStatus" : {
    "_id" : {
        "$binary" : "RtegzGJrakmEWnS26ffviQ==",
        "$type" : "03"
    },
    "JobId" : 3,
    "Status" : 1,
    "Created" : {
        "$date" : 1631895760470
    }
},
"Created" : {
    "$date" : 1631890789396
}
},
{
"_id" : 3,
"Requestor" : {
    "Info" : {
        "_id" : "175",
        "Name" : "Mary"
    },
},
"JobStatus" : {
    "_id" : {
        "$binary" : "RtegzGJrakmEWnS26ffviQ==",
        "$type" : "03"
    },
    "JobId" : 3,
    "Status" : 1,
    "Created" : {
        "$date" : 1631895760470
    }
},
"Created" : {
    "$date" : 1631890789396
}
}
aggregation-framework mongodb-query mongodb
1 Answers

You want to be using $group for this, like so:

db.collection.aggregate([
  {
    $match: {
      "Requestor.Info._id": "155"
    }
  },
  {
    $group: {
      _id: null,
      Open: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$JobStatus.Status",
                0
              ]
            },
            1,
            0
          ]
        }
      },
      Closed: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$JobStatus.Status",
                1
              ]
            },
            1,
            0
          ]
        }
      },
      
    }
  }
])

Mongo Playground

user_107703700
Related