MongoDB Aggregate two fields from collection independently and count-CodePudding

I have data in a MongoDB collection that looks like this:

{id: 11111, up: 450000, down: 452000}

I currently am able to group by one of the fields and count by doing the following (I am using PyMongo):

    {
        "$group": {
            "_id": {"up": "$up",},
            "count": {"$sum": 1}
        }
    },
    {"$project": {"_id": 0, "label": "$_id.up", "count": "$count"}},


    {"$sort": {"label": 1}},
    {"$match": {"count": {"$gt": 0}}},

And this gives me the following result:

{
   {
      "count": 35,
      "label": 450000
   }
}

I am trying to get both the "up" and "down" fields to appear in the following format:

{
   {
      "count": 35,
      "label": 450000
   },
   {
      "count": 35,
      "label": 452000
   }
}

Hoping someone can help me perform this query. The closest I can manage is by putting both "up" and "down" in an array under "label", but I would like them to be independent of each other.

Thank you!

CodePudding user response：

We can $project with $objectToArray to split up and down into an array of objects then $unwind to get separate documents for up and down, then just proceed with the same as above to count by label:

db.collection.aggregate([
    {
        "$project": {
            "updown": {
                "$objectToArray": {
                    "up": "$up",
                    "down": "$down"
                }
            }
        }
    },
    {"$unwind": "$updown"},
    {
        "$group": {
            "_id": {
                "label": "$updown.v"
            },
            "count": {"$sum": 1}
        }
    },
    {"$project": {"_id": 0, "label": "$_id.label", "count": "$count"}},
    {"$sort": {"label": 1}},
    {"$match": {"count": {"$gt": 0}}}
])

Result:

[
    {"count": 4, "label": 450000},
    {"count": 2, "label": 452000},
    {"count": 2, "label": 462000}
]

mongoplayground

Sample Data Setup:

from pymongo import MongoClient

client = MongoClient()
db = client.test

# Remove Collection if exists
db.collection.drop()
# Insert Sample Data
db.collection.insert_many([{'id': 11111, 'up': 450000, 'down': 452000},
                           {'id': 11112, 'up': 450000, 'down': 462000},
                           {'id': 11113, 'up': 450000, 'down': 452000},
                           {'id': 11114, 'up': 450000, 'down': 462000}])