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" : "[email protected]",
    "name" : "xyz"
}

Asset:

{
    "_id" : ObjectId("5485asasansja"),
    "users" : ["[email protected]", "[email protected]"],
    "remote" : { "bytes" : 123,
                  "source" : "some source"
               }
},
{
    "_id" : ObjectId("5885asaskaia"),
    "users" : ["[email protected]", "[email protected]"],
    "remote" : { "bytes" : 345,
                  "source" : "another source"
               }
}

I want to get results using pipeline like

{
    "[email protected]" : {"some source": 45845, "another source": 5845},
    "[email protected]" : {"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?

Akrion · Accepted Answer · 2018-11-28 20:49:06Z

2

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

answered Nov 28, 2018 at 20:49

Akrion

18.6k1 gold badge38 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

MongoEngine Aggregation - pivot array to object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related