CODEWITHSUNDEEP
javascriptmongodbmongodb-queryaggregation-framework
Kake_Fisk
Kake_Fisk

Reputation: 1165

How to count number of subdocuments with condition

I have a mongoDB collection with documents like the one bellow. I want to cumulatively, over all documents, count how many subdocuments that the event field has, which is not null.

{
    name: "name1",
    events: {
        created: {
            timestamp: 1512477520951
        },
        edited: {
            timestamp: 1512638551022
        },
        deleted: null
    }
}



{
    name: "name2",
    events: {
        created: {
            timestamp: 1512649915779
        },
        edited: null,
        deleted: null
    }
}

So the result of the query on these two documents should return 3, because there are 3 events that is not null in the collection. I can not change the format of the document to have the event field be an array.

Upvotes: 0

Views: 274

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151180

You want $objectToArray from MongoDB 3.4.7 or greater in order to do this as an aggregation statement:

db.collection.aggregate([
  { "$group": {
    "_id": null,
    "total": {
      "$sum": {
        "$size": {
          "$filter": {
            "input": {
              "$objectToArray": "$events"
            },
            "cond": { "$ne": [ "$$this.v", null ] }
          }
        }
      }
    }
  }}
])

That part is needed to look at the "events" object and translate each of the "key/value" pairs into array entries. In this way you can apply the $filter operation in order to remove the null "values" ( the "v" property ) and then use $size in order to count the matching list.

All of that is done under a $group pipeline stage using the $sum accumulator

Or if you don't have a supporting version, you need mapReduce and JavaScript execution in order to to the same "object to array" operation:

db.collection.mapReduce(
  function() {
    emit(null,
      Object.keys(this.events).filter(k => this.events[k] != null).length);
  },
  function(key,values) {
    return Array.sum(values);
  },
  { out: { inline: 1 } }
)

That uses the same basic process by obtaining the object keys as an array and rejecting those where the value is found to be null, then obtaining the length of the resulting array.

Because of the JavaScript evaluation, this is much slower than the aggregation framework counterpart. But it's really a question of what server version you have available to support what you need.

Upvotes: 1

Related Questions