Joining in NoSQL to get chat messages for a user

[Laycee]

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();

[magpie]

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 https://mongoplayground.net/p/u8854tctPt6 .