How to get count of multiple fields based on value in mongodb?

Question

Collection exists as below:

[
    {"currentLocation": "Chennai", "baseLocation": "Bengaluru"},
    {"currentLocation": "Chennai", "baseLocation": "Bengaluru"},
    {"currentLocation": "Delhi", "baseLocation": "Bengaluru"},
    {"currentLocation": "Chennai", "baseLocation": "Chennai"}
]

Expected Output:

[
    {"city": "Chennai", "currentLocationCount": 3, "baseLocationCount": 1},
    {"city": "Bengaluru", "currentLocationCount": 0, "baseLocationCount": 3},
    {"city": "Delhi", "currentLocationCount": 1, "baseLocationCount": 0}
]

What I have tried is:

db.getCollection('users').aggregate([{
        $group: {
            "_id": "$baselocation",
            baseLocationCount: {
                $sum: 1
            }
        },
    }, {
        $project: {
            "_id": 0,
            "city": "$_id",
            "baseLocationCount": 1
        }
}])

Got result as:

[
    {"city": "Chennai", "baseLocationCount": 1},
    {"city": "Bengaluru", "baseLocationCount": "3"}
]

I'm not familiar with mongo, so any help?

MongoDB Version - 3.4

check the answers here: https://stackoverflow.com/questions/45307580/group-by-day-with-multiple-date-fields/45312816#45312816 — dnickless, Aug 03 '17 at 11:35

dnickless · Accepted Answer · 2017-09-25T08:58:42.343

Neil Lunn and myself had a lovely argument over this topic the other day which you can read all about here: Group by day with Multiple Date Fields.

Here are two solutions to your precise problem.

The first one uses the $facet stage. Bear in mind, though, that it may not be suitable for large collections because $facet produces a single (potentially huge) document that might be bigger than the current MongoDB document size limit of 16MB (which only applies to the result document and wouldn't be a problem during pipeline processing anyway):

collection.aggregate(
  {
    $facet:
    {
      "current":
      [
        {
          $group:
          {
            "_id": "$currentLocation",
            "currentLocationCount": { $sum: 1 }
          }
        }
      ],
      "base":
      [
        {
          $group:
          {
            "_id": "$baseLocation",
            "baseLocationCount": { $sum: 1 }
          }
        }
      ]
    }
  },
  { $project: { "result": { $setUnion: [ "$current", "$base" ] } } }, // merge results into new array
  { $unwind: "$result" }, // unwind array into individual documents
  { $replaceRoot: { newRoot: "$result" } }, // get rid of the additional field level
  { $group: { "_id": "$_id", "currentLocationCount": { $sum: "$currentLocationCount" }, "baseLocationCount": { $sum: "$baseLocationCount" } } }, // group into final result)
  { $project: { "_id": 0, "city": "$_id", "currentLocationCount": 1, "baseLocationCount": 1 } } // group into final result
)

The second one works based on the $map stage instead:

collection.aggregate(
  {
    "$project": {
      "city": {
        "$map": {
          "input": [ "current", "base" ],
          "as": "type",
          "in": {
            "type": "$$type",
            "name": {
              "$cond": {
                "if": { "$eq": [ "$$type", "current" ] },
                "then": "$currentLocation",
                "else": "$baseLocation"
              }
            }
          }
        }
      }
    }
  },
  { "$unwind": "$city" },
  {
    "$group": {
      "_id": "$city.name",
      "currentLocationCount": {
        "$sum": {
          "$cond": {
            "if": { "$eq": [ "$city.type", "current" ]  },
            "then": 1,
            "else": 0
          }
        }
      },
      "baseLocationCount": {
        "$sum": {
          "$cond": {
            "if": { "$eq": [ "$city.type", "base" ]  },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  }
)

Thanks Nick! Works perfectly! – Sakthivel Aug 04 '17 at 04:46 — Sakthivel, Aug 04 '17 at 04:46

How to get count of multiple fields based on value in mongodb?

1 Answers1