CODEWITHSUNDEEP
mongodbaggregation-framework
It'sNotMe
It'sNotMe

Reputation: 1260

MongoDB aggregate nested values

In a MongoDB collection, there is data nested in an absence array.

{
"_id" : ObjectId("5c6c62f3d0e85e6ae3a8c842"),
"absence" : [
    {
        "date" : ISODate("2017-05-10T17:00:00.000-07:00"),
        "code" : "E",
        "type" : "E",
        "isPartial" : false
    },
    {
        "date" : ISODate("2018-02-24T16:00:00.000-08:00"),
        "code" : "W",
        "type" : "E",
        "isPartial" : false
    },
    {
        "date" : ISODate("2018-02-23T16:00:00.000-08:00"),
        "code" : "E",
        "type" : "E",
        "isPartial" : false
    },
    {
        "date" : ISODate("2018-02-21T16:00:00.000-08:00"),
        "code" : "U",
        "type" : "U",
        "isPartial" : false
    },
    {
        "date" : ISODate("2018-02-20T16:00:00.000-08:00"),
        "code" : "R",
        "type" : "E",
        "isPartial" : false
    }
]
}

I'd like to aggregate by absence.type to return a count of every type and the total number of absence children. The results might look like:

{
    "_id" : ObjectId("5c6c62f3d0e85e6ae3a8c842"),
    "U" : 1,
    "E" : 4,
    "total" : 5
}

There are several similar questions posted here but I'm yet to successfully adapt the answers my schema. Any help is greatly appreciated.

Also, are there GUI modeling tools to help with MongoDB query building? The transition from RDBMS queries to the Mongo aggregation pipeline has been quite difficult.

Upvotes: 1

Views: 900

Answers (2)

gbackmania
gbackmania

Reputation: 890

If you know all the possible values of "absence.type" then $filter the array on the value and compute the $size of the filtered array. This won't work if you don't know all the possible values in the "absence.type".

db.col.aggregate([

    { $project: { U: { $size: { $filter: { input: "$absence", as: "a", cond: { $eq: [ "$$a.type", "U"]}  }}}, 
                    E: { $size: { $filter: { input: "$absence", as: "a", cond: { $eq: [ "$$a.type", "E"]}  }}} }},
    { $project: { total: { $add: [ "$U", "$E" ]}, U: 1, E: 1}},

])

Upvotes: 0

mickl
mickl

Reputation: 49945

You can use below aggregation:

db.col.aggregate([
    {  
        $unwind: "$absence" 
    },
    {
        $group: {
            _id: { _id: "$_id", type: "$absence.type" },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id._id",
            types: { $push: { k: "$_id.type", v: "$count" } },
            total: { $sum: "$count" }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: [ "$$ROOT", { $arrayToObject: "$types" } ]
            }
        }
    },
    {
        $project: {
            types: 0
        }
    }
])

$unwind allows you to get single document per absence. Then you need double $group, first one to count by type and _id and second one to aggregate the data per _id. Having one document per _id you just need $replaceRoot with $mergeObjects to promote your dynamically created keys and values (by $arrayToObject) to the root level.

output:

{ "_id" : ObjectId("5c6c62f3d0e85e6ae3a8c842"), "total" : 5, "U" : 1, "E" : 4 }

Upvotes: 2

Related Questions