pymongo aggregate with $lookup and pipeline-CodePudding

I'm using python3, pymongo, mongodb4.4.3

I have two collections:

assignments: user_id,qid
questions: qid,text,category

When I join them in one query base on qid it looks like this:

aggregate = [
        {
            '$match': {
                'user_id': user_id
            }
        },
        {
            '$lookup': {
                'from': 'questions',
                'localField': 'qid',
                'foreignField': 'qid',
                'as': 'question'
            }
        }
    ]
result = col_assignments.aggregate(aggregate)

And it works perfectly.

But now I need to filter based on 'category' field in 'questions' collection.

Internet says I need to use pipeline with $expr instead of local and foreign fields. So I did a query like this:

aggregate = [
        {
            '$match': {
                'user_id': user_id
            }
        },
        {
            '$lookup': {
                'from': 'questions',
                'let': { 'qid': '$qid' },
                'pipeline': [{ 
                    '$match': { 
                        '$expr': {'$eq': ['$$qid', 'qid']},
                        'category': current_category
                    }
                }],
                'as': 'question'
            }
        }
    ]

And it won't work, 'question' is empty.

I'm guessing it has something to do with syntax. But I've never used such complex queries neither in mongo shell, nor in pymongo. Can you help me with that?

CodePudding user response：

If you want to filter out the whole assignment item based on corresponding question category, you should not define the condition on category inside $lookup aggregation stage. Instead use another separate $match stage.

aggregate = [
        {
            '$match': {
                'user_id': user_id
            }
        },
        {
            '$lookup': {
                'from': 'questions',
                'localField': 'qid',
                'foreignField': 'qid',
                'as': 'question'
            }
        },
        {
            '$match': {
                'question.0.category': current_category
            }
        }
])

If you want to filter out only the corresponding question category, just add missing $ as ray suggests: '$expr': {'$eq': ['$$qid', '$qid']}.