MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Edit query to output multiple IDs

I need a query that takes multiple ‘companyID’s’ and return the count for each company.

Currently this query only does this for one companyID and it does not return the id but just ‘null’ like show below.

I understand that I can use the ‘in’ operator for multiple companyID’s but not sure how I would go about having the query return multiple objects of counts for each companyID.

db.getCollection('reg').aggregate([{
    '$match': {
        '$and': [
            {
                'companyID': 11
            },
            {
                'created': {
                    '$gte': 1556726597
                }
            },
            {
                'created': {
                    $lt: 1580572997
                }
            }
        ]
    }
},
{
    '$project': {

        'testID': 1,

    }
},
{
    '$group': {
        '_id': '$testID',
        'registrationsCount': { '$sum': 1 },
    },
},
{
    $group: {
        _id: null,
        count: { $sum: 1 }
    }
}
]) 

The result below

{
    "_id" : null,
    "count" : 10.0
} 

Schema below

{
    "_id" : NumberLong(1),
    "appUserID" : NumberLong(4294967295),
    "companyID" : NumberLong(5),
    "created" : NumberLong(1372625588),
    "testID" : NumberLong(11),
    "isCheckIn" : true,
    "lastModified" : NumberLong(1372625588),
    "source" : "upload",
    "timeArrived" : NumberLong(1343062512),
}

I need a query that takes multiple ‘companyID’s’ and return the count for each company.

I understand that I can use the ‘in’ operator for multiple companyID’s but not sure how I would go about having the query return multiple objects of counts for each companyID.

Consider the following documents in a test collection (similar to that of yours):

{ _id: 1, companyID: 12, testID: 411, created: 1556726597 },
{ _id: 2, companyID: 12, testID: 612, created: 1556726598 },
{ _id: 3, companyID: 15, testID: 913, created: 1556726599 },      // created is out of range
{ _id: 4, companyID: 19, testID: 814, created: 1556726586 },      // companyID doesn't match
{ _id: 5, companyID: 12, testID: 215, created: 1556726588 },
{ _id: 6, companyID: 15, testID: 719, created: 1556726591 }

The following aggregation

db.test.aggregate( [
  { 
      $match: { 
          companyID: { $in: [ 12, 15 ] }, 
          created: { $gt: 1556726585, $lt: 1556726599 }
      } 
  },
  { 
      $group: { 
          _id: "$companyID", 
          registrationsCount: { $sum: 1 } 
      } 
  },
  { 
      $project: { 
          companyID: "$_id", 
          registrationsCount: 1, 
          _id: 0 
      } 
  }
] )

returns:

{ "registrationsCount" : 1, "companyID" : 15 }
{ "registrationsCount" : 3, "companyID" : 12 }

Reference: $in query operator used in the above aggregation’s $match stage.

@Prasad_Saya Thanks for this but I not exactly what I was after. companyID can have multiple testID;s. So, I was counting all test ids that had a regcount over 1. Only issue was that it returns id as null.

I need a way to include multiple company ids that returns the test counts for each of the companyIDs entered.

Please post what a sample output document looks like.

@Prasad_Saya below is the response, this means that company had 10 testID’s with a regcount of 1 or more. The issue is that the second $group makes the id null, which i want as the companyid. The next issue is I want to make the query return a object like below for each companyID I put in the query

{
“_id” : null,
“count” : 10.0
}

It is not quite clear to me, I am afraid. But, I think the representation of data (after counting the testID’s for the companies) and the output I am thinking is like this:

companyID  testID  count
11           112      2
11           119      1
11           120      5
12           145      3
12           290      2

{ "companyID " : 11, "count" : 8 }
{ "companyID " : 12, “count" : 5 }

@Prasad_Saya That output is correct, Just for clarity - the query you proposed does not output this correct? because the count would need derive from testID

Actually, the count from my previous query returns the same output. Is there separate count field for each testID in the input schema (testID is an identifier, not a counter, I think)?

@Prasad_Saya sorry Im still beginner level - the below may help clear up;

This is the same query as posted by the second group commented out

db.getCollection('registrations').aggregate([        {
                '$match' : {
                        '$and' : [
                                {
                                        'companyID'        : 7837
                                },
                                {
                                        'created' : {
                                                '$gte' : 1532131200
                                        }
                                },
                                {
                                        'created' : {
                                                $lt : 1556560058
                                        }
                                }
                        ]
                }
        },
        {
                '$project' : {
                        'eventID' : 1
                }
        },
        {
                '$group' : {
                        '_id'                : '$eventID',
                        'registrationsCount' : {'$sum' : 1},
                },
        }
//         { 
//             $group: { 
//                 _id: null, 
//                 count: { $sum: 1 } 
//             } 
//         }
])

This is the output - as you can each “_id” is now the testID and each has their own registrationsCount

{
    "_id" : 240399,
    "registrationsCount" : 23.0
}

/* 2 */
{
    "_id" : 238853,
    "registrationsCount" : 23.0
}

/* 3 */
{
    "_id" : 238104,
    "registrationsCount" : 2.0
}

/* 4 */
{
    "_id" : 237096,
    "registrationsCount" : 49.0
}

The output from original query would be the below;

{
    "_id" : null,
    "count" : 4.0
}

@Prasad_Saya $eventID is the same as when I mention $testID - I just changed for this post