MongoDB aggregating multiple objects

Question

I'm trying to create a MongoDB query where I get a conversation between two users. Basically my conversation object is like the following:

{
    "_id" : ObjectId("123"),
    "from" : ObjectId("456"),
    "to" : ObjectId("789"),
    "content" : "Hi!",
    "createdAt" : 1558037545568.0
}

I wanna bring up their conversation sorted by createdAt descending and also bring their user objects together. I know I need to use a $lookup for search on the users query, but I have some questions:

How I can create a $lookup operation that will bring both of the users?
How to manage it in the way that sometimes the user 456 is the from user and sometimes the to user? Same for the user 789. I didn't get it too much.

This is the current query that brings everything ordered without the user objects:

db.getCollection('messages').aggregate([{
    $match: {
      $or: [
        { from: ObjectId("456") },
        { to: ObjectId("789") },
        { from: ObjectId("789") },
        { to: ObjectId("456") }
      ]
    }
  }, {
    $sort: { createdAt: -1 }
  }])

Thank y'all for the help!

mickl · Accepted Answer · 2019-05-24 08:57:15Z

2

You can "normalize" your documents by adding users array which will contain from and to sorted ascending. Then it's easier to run $match on that array (sorting arguments),

db.collection.aggregate([
    {
    $addFields: {
        users: [ { $min: [ "$from", "$to" ] }, { $max: [ "$from", "$to" ] } ]
    } 
    },
    {
        $match: { users: [ "456", "789" ] }
    }
])

$lookup can be run directly on an array so following syntax should work as a next step:

{
    $lookup: {
        from: "users",
        localField: "users",
        foreignField: "_id",
        as: "users"
    }
}

answered May 24, 2019 at 8:57

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

napfernandes Over a year ago

Thanks @mickl! Also, I'm struggling now to replace those results into the rootObject. Based on your solution, I have an array of users and I want to replace them into the main object: the from attribute will be one of the user object and the to attribute as well. I was looking to the replaceRoot pipeline, but I didn't find any way to compare whatever I have in the users array with my main attributes. Any suggestion? Thanks!

mickl Over a year ago

@Kiwanax could you describe that as a separate question and paste a link here ? I will try to take a look later

Collectives™ on Stack Overflow

MongoDB aggregating multiple objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related