mongodb apply match to lookup results

Question

If I have a user and post collection

{"_id": 1, "name": "User 1"}
{"_id": 2, "name": "User 2"}
{"_id": 3, "name": "User 3"}

{"_id": 1, "title": "Post 1", "category": "TRAVEL", "userId": 1, "createdAt": ISODate("2017-07-24T04:12:54.255Z")}
{"_id": 2, "title": "Post 2", "category": "TUTORIAL", "userId": 1, "createdAt": ISODate("2017-07-25T04:12:54.255Z")}
{"_id": 3, "title": "Post 1", "category": "TRAVEL", "userId": 2, "createdAt": ISODate("2017-07-24T04:12:54.255Z")}

I want to return all users with their latest post. If they have posts, I just need one in TUTORIAL category. Otherwise the post field can be empty. I have tried this

db.getCollection('user').aggregate([
    {$lookup: {from: "post", localField: "_id", foreignField: "userId", as: "post"}},
    {$unwind: { path: "$post", preserveNullAndEmptyArrays: true }},
    {$match: {$or: [{"post.category": "TUTORIAL"}, {post: {$exists: false}}]}},
    {$sort: {"post.createdAt": -1}},
    {$group: {"_id": "$_id", "name": {$first: "$name"}, "post": {$first: "$post"}},
    {$project: {"_id": 1, "name": 1, post": 1}}
])

but it only return User 1 and User 3, because:

User 1 has 1 post in TUTORIAL and it matches $match condition
User 2 only have 1 post in TRAVEL and it does not match $match condition
User 3 has no post but it matches $match condition

How can I list ALL users with their latest post in TUTORIAL category but still include users that do not have post?

@Shubham hey you are right! I just posted my answer down below. Cheers. — spondbob, Dec 01 '17 at 04:09

score 0 · Accepted Answer · answered Dec 01 '17 at 04:09

As per this answer and @Shubham's comment, I come up with this

db.getCollection('user').aggregate([
    {$lookup: {from: "post", localField: "_id", foreignField: "userId", as: "posts"}},
    {$project: {"posts": {$filter: {input: "$posts", as: "post", "cond": {$eq: ["$$post.category", "TUTORIAL"]}}}, "_id": 1, "name": 1}}
    {$unwind: { path: "$posts", preserveNullAndEmptyArrays: true }},
    {$sort: {"posts.createdAt": -1}},
    {$group: {"_id": "$_id", "name": {$first: "$name"}, "posts": {$first: "$posts"}},
    {$project: {"_id": 1, "name": 1, posts": 1}}
])

Notice the first projection where I use $filter to "query" results from $lookup then "flatten" it with $unwind

mongodb apply match to lookup results

1 Answers1