CODEWITHSUNDEEP
node.jsmongodb
Miguel
Miguel

Reputation: 141

Merge two MongoDB aggregates into one pipeline

I have a collection of population census and I still don't dominate the “aggregate” function to get the results with just one query.

The collection has this format (plus a ISO 8601 timestamp). This way, each time a census is conducted we can register the current ages and counts (which can add/modify/delete previous ages).

Now I have two “aggregate” queries to return this:

  1. Get the AVG, MAX, MIN of all the registries in the DB.
  2. Get each age and show a total (“sum”) of people with that age.

However I need to get those results with just one “aggregate” query, but the pipeline is somewhat difficult to me, and I cannot get the statistics and then “unwind” the population to get the sums…

Any help on how to merge this two queries, please? Thank you all in advance!

Upvotes: 3

Views: 2709

Answers (1)

chridam
chridam

Reputation: 103365

Try the following aggregation pipeline, it should give you result of the two queries:

db.collection('population').aggregate([
    {
        "$unwind": "$population"
    },
    {
        "$group": {
            "_id": 0,
            "doc": {
                "$push": "$$ROOT"
            },
            "average_age": {
                "$avg": "$population.age"
            },
            "max_age": {
                "$max": "$population.age"
            },
            "min_age": {
                "$min": "$population.age"
            },
            "average_population": {
                "$avg": "$population.count"
            },
            "max_population": {
                "$max": "$population.count"
            },
            "min_population": {
                "$min": "$population.count"
            }
        }
    },
    {
        "$unwind": "$doc"
    },
    {
        "$group": {
            "_id": "$doc.population.age",
            "sum": { "$sum": "$doc.population.count" },
            "average_age": { "$first": "$average_age"},
            "max_age": { "$first": "$max_age"},
            "min_age": { "$first": "$min_age"},
            "average_population": { "$first": "$average_population"},
            "max_population": { "$first": "$max_population"},
            "min_population": { "$first": "$min_population"}
        }
    }
])

Upvotes: 4

Related Questions