MongoEngine Aggregation - pivot array to object

Question

I am working on following MongoEngine classes

class User(Document):
    username = EmailField(primary_key=True)
    name = StringField()

class Asset(Document):
    users = ListField( ReferenceField("User") )
    remote = MapField( DynamicField() )

Sample documents of the above classes are

User:

{
    "_id" : "xyz@xyz.com",
    "name" : "xyz"
}

Asset:

{
    "_id" : ObjectId("5485asasansja"),
    "users" : ["xyz@xyz.com", "abc@abc.com"],
    "remote" : { "bytes" : 123,
                  "source" : "some source"
               }
},
{
    "_id" : ObjectId("5885asaskaia"),
    "users" : ["pqr@xyz.com", "abc@abc.com"],
    "remote" : { "bytes" : 345,
                  "source" : "another source"
               }
}

I want to get results using pipeline like

{
    "xyz@xyz.com" : {"some source": 45845, "another source": 5845},
    "abc@abc.com" : {"some source": 584, "another source": 986}
}

bottom line, want to get the sum of bytes according to its source for each user.

I tried with

pipeline = [

        {'$project':
             {'remote.source': 1, 'remote.bytes': 1 }},

        {'$unwind': '$users'},

        {'$lookup': {
            'from': 'user', 'localField': 'users', 'foreignField' : '_id', 'as':'user'
        }},

        {'$match': {'remote.source': {'$exists': True, '$ne': None}}},
        {'$project': {'username': '$user.username'}},

        {'$group':
             {'username':'$username','source': '$remote.source', 'count': {'$sum': 1}, 'size': {'$sum': '$remote.bytes'}}}
    ]

for each in Asset.objects().aggregate(*pipeline):
    print(each)

It does not return any result. Can anyone help?

score 2 · Answer 1 · answered Nov 28 '18 at 20:49

See if this would do it for you:

db.collection.aggregate([
  { $unwind: "$users" },
  {
    "$group": {
      "_id": null,
      "data": {
        "$push": {
          "k": "$users",
          "v": "$remote"
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": "$data"
      }
    }
  }
])

The idea is to $unwind the users then $group them via data which is a key/value array in order to use $arrayToObject to form the final output object you are after.

You can see it here working

MongoEngine Aggregation - pivot array to object

1 Answers1