Skip to content
Advertisement

How to get common elements in array using project in pymongo aggregate

The following code gets the count of type for each user separately.

DB.ticket.aggregate([
        {
            '$match': {'project': project_id},
        },
        {
            '$group': { 
                '_id': {
                    'assignee': "$assignee.uid",
                    'type': "$type"
                }, 
                'count': { '$sum': 1} 
            },
            
        },
        {
            "$project": {'assignee': "$_id.assignee", 'type': "$_id.type", 'count': 1, "_id":0}
        }
    ])

Following is the output.

[
    {
        "assignee": "John",
        "count": 2,
        "type": "Open"
    },
    {
        "assignee": "John",
        "count": 3,
        "type": "Completed"
    },
    {
        "assignee": "Jason",
        "count": 2,
        "type": "In Progress"
    },
    {
        "assignee": "Jason",
        "count": 2,
        "type": "Completed"
    }
]

I want the following output, wherein pymongo aggregation project section code tweaks the output to show the ouput listed based on one primary key element which is the assignee in this case.

“John”: [
        {
            "count": 2,
            "type": "Open"
        },
        {
            "count": 3,
            "type": "Completed"
        }
    ],
“Jason”: [
        {
            "count": 2,
            "type": "In Progress"
        },
        {
            "count": 2,
            "type": "Completed"
        }
    ]

Advertisement

Answer

One option is to add 3 more steps:

  1. $group by the assignee
  2. $group to insert all documents into one document
  3. Create an object (dictionary) using $arrayToObject
  {$group: {
      _id: "$assignee",
      v: {$push: {count: "$count", type: "$type"}}
  }},
  {$group: {
      _id: 0,
      data: {$push: {k: "$_id", v: "$v"}}
    }
  },
  {$replaceRoot: {newRoot: {$arrayToObject: "$data"}}}
])

See how it works on the playground example

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement