Joining in NoSQL to get chat messages for a user

Question

In my chat app i have private / group chats and I need to get messages for a user.

Let me first explain the whole system.

Messages:

// group message
{
  id: 1,
  text: 'hello',
  chat_id: 'AjK954_e5iP',
  created_at: 137615...
},

// private message
{
  id: 2,
  text: ':)',
  chat_id: 'Bho9Sr_f51Rz',
  created_at: 137622...
}

Chats:

{
  id: 'AjK954_e5iP',
  type: 'group',
  title: 'My Group yay'
},
{
  id: 'Bho9Sr_f51Rz',
  type: 'private',
  participants: ['A', 'B']
}

Members:
When we join a group / private chat a new record is created in a table called members.

// group chat member
{
  user_id: 'A',
  chat_id: 'AjK954_e5iP',
  type: 'group'
}

// private chat member
{
  user_id: 'A',
  chat_id: 'Bho9Sr_f51Rz',
  type: 'private'
}

The Problem

to get the messages i need to perform a join on members and messages but it's not possible in NoSQL. i could solve the problem for private chats but for group chats an inner join is required.

SQL way:

SELECT messages.* from `members`
    INNER JOIN `messages`.`chat_id` = `members`.`chat_id`
         WHERE `members`.`user_id` = 'A'

Bad solution

If there are thousands of chat_ids, doing a where in is slow.

// Pseudocode

chat_ids = Members.where('user_id', 'A').get(['chat_id']);
chat_ids = chat_ids.map(item => item.chat_id);

messages = Messages.whereIn('chat_id', chat_ids).orderBy('created_at').limit(20).get();

Answer 1

I'm a MongoDB noob, but does this do what you want?

db.members.aggregate([
  {
    "$match": {
      user_id: "A"
    }
  },
  {
    "$lookup": {
      "from": "messages",
      "localField": "chat_id",
      "foreignField": "chat_id",
      "as": "thechats"
    }
  },
  {
    "$unwind": "$thechats"
  },
  {
    "$replaceWith": "$thechats"
  },
  {
    "$sort": {
      created_at: -1
    }
  },
  {
    "$limit": 20
  }
])

Try it at mongoplayground.net.