condition based lookup in mongodb

Question

I am new to MongoDB, and node.js need help in mongo lookup. I want to join two documents based on a condition. I am using mongoose, node, and express. In the aggregation of a Conversation model I want to look up the other model User.

This is user1

{
    "_id": "0b3bb0bb2d78405fa941dd3e1da9574a",
    "status": true,
    "fullName": "Irtza",
    "mobileNumber": "+9200000000000",
    "createdAt": {
        "$date": "2021-05-05T11:39:00.875Z"
    },
    "updatedAt": {
        "$date": "2021-06-04T11:10:53.103Z"
    },
    "__v": 0,
}

This is user2

{
    "_id": "51701df06d084003853cd0837d76606a",
    "status": true,
    "fullName": "Shehroz Ali",
    "mobileNumber": "+10000000000",
    "createdAt": {
        "$date": "2021-05-06T11:28:48.980Z"
    },
    "updatedAt": {
        "$date": "2021-06-04T12:34:03.676Z"
    },
    "__v": 0,
}

And there is a conversation between them

{
    "_id": "9a4d221ebbde4ba68b28783232aed28c",
    "userIds": ["0b3bb0bb2d78405fa941dd3e1da9574a", "51701df06d084003853cd0837d76606a"],
    "type": "private",
    "conversationName": "abc",
    "createdAt": {
        "$date": "2021-06-04T10:46:43.061Z"
    },
    "updatedAt": {
        "$date": "2021-06-04T10:46:43.061Z"
    },
    "__v": 0
}

So base on the above scenario I want to lookup/join a conversation with the user whose Id is not passing to Conversation.

There is a requested userId based on userId I want to lookup the User model. Also in my Conversation I have an array of string "userIds": ["0b3bb0bb2d78405fa941dd3e1da9574a", "51701df06d084003853cd0837d76606a"] if userIds.0 match with the userId then lookup with userIds.1 and if userIds.1 match with the userId then lookup with userIds.0 Here is my code:

this.aggregate([
                { $match: { userIds: { $all: [userId] } } },
                {
                    $lookup: {
                        from: 'users',
                        localField: 'userIds.0',
                        foreignField: '_id',
                        as: 'user1',
                    }
                },
                { $unwind: "$user1" },
                {
                    $lookup: {
                        from: 'users',
                        localField: 'userIds.1',
                        foreignField: '_id',
                        as: 'user2',
                    }
                },
                { $unwind: "$user2" },
                {
                    $lookup: {
                        from: 'messages',
                        localField: '_id',
                        foreignField: 'conversationId',
                        as: 'messageBody',
                    }
                },
                { "$addFields": {
                    "messageBody": {
                        "$arrayElemAt": [ "$messageBody", -1 ]
                    }
                }},
                { $unwind: "$messageBody" },
            ]);

user_14389830 · Answer

You should first use $unwind, and then $lookup. You can do it like this:

$match to filter our Conversations where the requested userId is present in userIds arrays
$unwind to deconstructs an userIds array field
$match again to filter only requested userId
$lookup to replace userId with actual User document

db.conversations.aggregate([
  {
    "$match": {
      "userIds": "0b3bb0bb2d78405fa941dd3e1da9574a"
    }
  },
  {
    "$unwind": "$userIds"
  },
  {
    "$match": {
      "userIds": {
        "$ne": "0b3bb0bb2d78405fa941dd3e1da9574a"
      }
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "userIds",
      "foreignField": "_id",
      "as": "userIds"
    }
  }
])

Here is the working example: https://mongoplayground.net/p/I5GyY2XMLkb

condition based lookup in mongodb

1 Answers