"Inner join" like with MongoDB in Flask, Jinja

Question

I'm trying to perform an 'inner join' like with two MongoDB collections using Flask.

I think I should be using $lookup to achieve this, and my route currently is:

@app.route("/perfumes")
def perfumes():
    perfumes = mongo.db.perfumes.find()
    users = mongo.db.perfumes.aggregate(
        [
            {
                "$lookup": {
                    "from": "mongo.db.users",
                    "localField": "author",
                    "foreignField": "username",
                    "as": "creator",
            }
        }
    ]
)
return render_template("perfumes.html", title="Perfumes", perfumes=perfumes, users=users)

In my template I'm iterating through the perfumes in one collection and would like to access all the data in the users collection, as each perfume item has been created by one user. The perfumes collection has an author field which should be used to link the username field in the users collection. A very simplified (stripped down) version of my template looks like this:

% for perfume in perfumes %}
    {{ perfume.name }}
    Created by {{ <HERE I WANT TO HAVE MY ITEM CREATOR> }} on {{ perfume.date_updated.strftime('%d-%m-%Y') }}
{% endfor %}

What am I missing in order to have this 'join' working and be able to access to all data for that item from both collections?

Thank you!!

NOTE: I want to access more than just the creator's username, like profile picture, etc, that's why I need the join.

UPDATE: These are the structures I'm using for both databases:

{
    "perfumes": {
        "author": "<string>",
        "brand": "<string>",
        "name": "<string>",
        "descritpion": "<text field>",
        "date_updated": "<date>",
        "public": "<boolean>",
        "picture": "<string>"
    },
    "users": {
        "username": "<string>",
        "first_name": "<string>",
        "last_name": "<string>",
        "email": "<string>",
        "is_admin": "<boolean>",
        "avatar": "<string>"
    }
}

So the author in perfumes would be linkable with the username in users.

Answer 1

You can do it with a simple aggregation. Try the below code:

cur = mongo.db.perfumes.aggregate(
    [
        {
            '$lookup': {
                'from': 'users', # You can directly specify on which collection you want to lookup
                'localField': 'author',
                'foreignField': 'username',
                'as': 'creator',
            }
        },
        {
            '$unwind' : '$creator'
        },
        {
            '$project': {
                'username' : '$creator.username',
                'perfumeName' : '$name',
                'date_updated' : '$date_updated',
                'profilePicture' : '$creator.avatar',
                ..., # Rest of the items that you want to display
                '_id': 0
            }
        }
    ]
)

Return the results like below:

return render_template("perfumes.html", title="Perfumes", perfumes=cur) # You don't need to pass users

Display in the template like below:

{% for perfume in perfumes %}
    {{ perfume['perfumeName'] }}
    Created by {{ perfume['username'] }} on {{ perfume['date_updated'].strftime('%d-%m-%Y') }}
{% endfor %}

I hope it helps.