CODEWITHSUNDEEP
mongodbaggregation-framework
napfernandes
napfernandes

Reputation: 1359

MongoDB aggregating multiple objects

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:

  1. How I can create a $lookup operation that will bring both of the users?
  2. 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!

Upvotes: 2

Views: 164

Answers (1)

mickl
mickl

Reputation: 49985

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"
    }
}

Upvotes: 2

Related Questions