Count multiple fields in MongoDB aggregation

mongodb_facet_and_unwind.md

Summary

Let's say we have the following documents:

[
  {"_id": "6094de60f74b0354af32dd17", "id": 1, "title": "Title1", "status": 1, "nested_field": {"expire_time": 4130633413} }
  {"_id": "6094de60f74b0354af32dd18", "id": 1, "title": "Title1", "status": 1}
  {"_id": "6094de60f74b0354af32dd19", "id": 2, "title": "Title2", "status": 0}
  {"_id": "6094de60f74b0354af32dd20", "id": 2, "title": "Title2", "status": 2}
  {"_id": "6094de60f74b0354af32dd21", "id": 3, "title": "Title3", "status": 0}
]

Now, we want to achieve the following things:

1. find the title field with the same id value
2. count documents where the status fields greater than value 0
3. count documents where the status fields greater than value 0 and nested_field.expire_time not expired (which is greater then now (new Date()).getTime())

Query

We could use the following query for mongodb:

db.collection_name.aggregate(
  [
    { "$match": { "id": 1, "status": { "$gt": 0 } } },
    {
      "$facet": {
        "title": [
          { "$group": { "_id": "$id", "title": { "$first": "$title" } } },
        ],
        "field1": [
          { "$match": { "status": { "$gt": 0 } } },
          {
            "$group": {
              "_id": 0, "count_num": { "$sum": 1 }
            }
          },
        ],
        "field2": [
          {
            "$match": {
              "status": { "$gt": 0 },
              "nested_field.expire_time": { "$gt": NumberLong(((new Date()).getTime() / 1000).toFixed(0)) }
            }
          },
          {
            "$group": {
              "_id": 0, "count_num": { "$sum": 1 }
            }
          }
        ]
      }
    },
    {
      "$project": {
        "_id": 0,
        "title": { "$cond": [{ "$eq": ["$title", []] }, [{ "title": "" }], "$title"] },
        "field1": { "$cond": [{ "$eq": ["$field1", []] }, [{ "count_num": 0 }], "$field1"] },
        "field2": { "$cond": [{ "$eq": ["$field2", []] }, [{ "count_num": 0 }], "$field2"] }
      }
    },
    { "$unwind": "$title" },
    { "$unwind": "$field1" },
    { "$unwind": "$field2" },
    { "$project": { "title": "$title.title", "field1": "$field1.count_num", "field2": "$field2.count_num" } }
  ]
)

Explaination

$facet

We cannot seperate grouped $sum query in just one $project operation. By using $facet, we are able to aggregate seperately.

$project with $cond

Since the aggregation might return empty result (empty array), we need to set the fallback default value for each possible aggregation fields (title, field1, field2 in this example). By using $cond with $project operation, we can conditionally determine whether the fields were empty or not, if it is empty, then assign a default value to it.

$unwind

After the previous $project operation, we could get the following example result:

{
  "title": [ { "title": "Some Title" } ],
  "field1": [ { "count_num": 1 } ],
  "field2": [ { "count_num": 2 } ]
}

These fields were returned as object array, which is hard to use. By using $unwind, we could flatten the a object array above like this:

{
  "title": { "title": "Some Title" },
  "field1": { "count_num": 1 },
  "field2": { "count_num": 2 }
}

$project

At the last, we reassign the fields value into the field names we want.

Query result

query "id": 1

{
  "title": "Title1",
  "field1": 2,
  "field2": 1
}

query "id": 2

{
  "title": "Title2",
  "field1": 1
  "field2": 0
}

query "id": 3

If not found any matched status or nested_field.expire_time, but found the title, then fallback field1 and field2 into default value

{
  "title": "Title3",
  "field1": 0
  "field2": 0
}

query "id": 4

It will fallback to default value

{
  "title": "",
  "field1": 0
  "field2": 0
}