Description
Describe the bug
The combination of $lookup stage followed by $unwind stage with option preserveNullAndEmptyArrays: true prevents filter from $match stage being applied.
Environment
- Operating system and version debian 12
- PostgreSQL version 17.5
- Architecture amd64
Reproduction Steps
Below shows the filter { $match: { title: "Emma" } } isn't applied to returned results.
test> db.authors.insertOne( { name: "Jane Austen" } )
{
acknowledged: true,
insertedId: ObjectId('68301b2bab136b903dc59f35')
}
test> db.books.insertMany( [ { title:"Pride and prejudice", author: "Jane Austen"}, {title:"Emma", author: "Jane Austen" } ] )
{
acknowledged: true,
insertedIds: {
'0': ObjectId('683023bfab136b903dc59f3c'),
'1': ObjectId('683023bfab136b903dc59f3d')
}
}
test> db.books.aggregate([
... { $lookup: { from: "authors", localField: "author", foreignField: "name", as: "author-names" } },
... { $unwind: { path: "$author-names", preserveNullAndEmptyArrays: true } },
... { $match: { title: "Emma" } }
... ] )
[
{
_id: ObjectId('683023bfab136b903dc59f3c'),
title: 'Pride and prejudice',
author: 'Jane Austen'
},
{
_id: ObjectId('683023bfab136b903dc59f3d'),
title: 'Emma',
author: 'Jane Austen',
'author-names': { _id: ObjectId('68302380ab136b903dc59f3b'), name: 'Jane Austen' }
}
]
Expected behavior
Aggregation stage $match filter is applied and only returns matching documents.
[
{
_id: ObjectId('683023bfab136b903dc59f3d'),
title: 'Emma',
author: 'Jane Austen',
'author-names': { _id: ObjectId('68302380ab136b903dc59f3b'), name: 'Jane Austen' }
}
]
Actual behavior
All documents in the collection are returned, regardless of the filter in $match stage.
Additional context
The expected behavior is seen if preserveNullAndEmptyArrays: false.