CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
ricky
ricky

Reputation: 2108

In MongoDb how to get the max\min\avg\count of a single matched element occurred inside an embedded array?

I have a MongoDB collection as below. And I want to get the min\max\avg\count of the xxx field inside all documents which $match: { "Parsed.FileId": "421462559", "Parsed.MessageId": "123" }

Note that Fields of each document only contains one single field with SchemaName = xxx

Is it possible with MongoDB aggregation (or other feature) and how?

{
    "_id" : NumberLong(409),
    "Parsed" : {
            "FileId" : "421462559",
            "MessageId": "123",
            "Fields" : [
                    {
                            "SchemaName" : "xxx",
                            "Type" : 0,
                            "Value" : 6
                    },
                    {
                            "SchemaName" : "yyy",
                            "Type" : 0,
                            "Value" : 5
                    }
            ]
    }
},
{
    "_id" : NumberLong(510),
    "Parsed" : {
            "FileId" : "421462559",
            "MessageId": "123",
            "Fields" : [
                    {
                            "SchemaName" : "xxx",
                            "Type" : 0,
                            "Value" : 10
                    },
                    {
                            "SchemaName" : "yyy",
                            "Type" : 0,
                            "Value" : 20
                    }
            ]
    }
}

For example collection above, I expect to get the result for field xxx as:

{
    count: 2,
    min: 6,
    max: 10,
    avg: 8
}

Upvotes: 2

Views: 2883

Answers (1)

Ashh
Ashh

Reputation: 46461

You can use below aggregation

Basically you need to first $unwind the nested array and then have to use $group with the corresponding accumulator i.e. min max $sum $avg

db.collection.aggregate([
  { "$match": { "Parsed.Fields.SchemaName": "xxx" }},
  { "$unwind": "$Parsed.Fields" },
  { "$match": { "Parsed.Fields.SchemaName": "xxx" }},
  { "$group": {
    "_id": null,
    "count": { "$sum": 1 },
    "max": { "$max": "$Parsed.Fields.Value" },
    "min": { "$min": "$Parsed.Fields.Value" },
    "avg": { "$avg": "$Parsed.Fields.Value" }
  }}
])

Upvotes: 2

Related Questions