Q:

Complex MongoDB query using MongoEngine

guys!
I'm trying MongoDB(version 3.2) and MongoEngine and want to make complex query, but totally lost on how could i make it. If it's even possible.
There is 4 documents:
class File(EmbeddedDocument):
    path = StringField()

class Episode(EmbeddedDocument):
    num = IntField()
    alias = StringField()
    files = EmbeddedDocumentListField('File')

class Season(Document):
    num = IntField()
    alias = StringField()
    episodes = EmbeddedDocumentListField('Episode', db_field='items')

class Series(Document):
    title = StringField()
    alias = StringField()
    description = StringField()
    seasons = ListField(ReferenceField('Season'), db_field='items')

I need to make query that will return response of the following form:
[{
   "path": "/series/<series alias>",
   "title": "<series title>",
   "description": "<series description>",
   "seasons": [{
                "path": "/series/<aseries alias>/<season alias>",
                "title": "Season <season num>",
                "episodes": [{
                    "path": "/series/<series alias>/<season alias>/<episode alias>",
                    "title": "Episode <episode num>",
                    "files": [{
                        "path": "<path>"
                    }]
                }]
             }]
}]

Is it possible to receive response like that with only one query?

A:

This is right query to get response like this:
cursor = Series.objects.all().aggregate(
        {'$unwind': '$items'},
        {'$lookup': {
            'from': 'products',
            'localField': 'items',
            'foreignField': '_id',
            'as': 'seasons'
        }},
        {'$project': {
            'path': {'$concat': ['/series/', '$alias']},
            'title': '$title',
            'description': '$description',
            'seasons': {
                '$map': {
                    'input': '$seasons',
                    'as': 'season',
                    'in': {
                        'path': {'$concat': ['/series/', '$alias', '/', '$$season.alias']},
                        'title': {'$concat': ['Season ', {'$substr': ['$$season.num', 0, -1]}]},
                        'episodes': {
                            '$map': {
                                'input': '$$season.items',
                                'as': 'episode',
                                'in': {
                                    'path': {'$concat': ['/series/', '$alias', '/', '$$season.alias', '/', '$$episode.alias']},
                                    'title': {'$concat': ['Episode ', {'$substr': ['$$episode.num', 0, -1]}]},
                                    'files': {
                                        '$map': {
                                            'input': '$$episode.files',
                                            'as': 'files',
                                            'in': {
                                                'path': '$$files.path'
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }})
res = list(cursor)[0]

