MongoDB aggregate nested values

Question

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.

mickl · Accepted Answer · 2019-02-26 22:24:47Z

2

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 }

answered Feb 26, 2019 at 22:24

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

It'sNotMe Over a year ago

That nearly did it, @mickl. Thank you. The root level object contains other fields I want to return in my projection. What do I need to do to project out those additional fields? Imagine there is a firstnamefield in the data at the same level as the absences array. Thanks!

mickl Over a year ago

You can add firstname: { $first: 'firstname' } in both $group stages

gbackmania · Accepted Answer · 2019-02-28 17:44:00Z

0

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}},

])

answered Feb 28, 2019 at 17:44

gbackmania

8903 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

MongoDB aggregate nested values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related