I am building a customer service chat application backend using Nestjs(Node) and MongoDB(mongoose) for managing users and their chat with a chatbot, For chatting its working as middleware between the frontend and chatbot server.
I have these collections in my database
- Users ( for saving basic user details )
Users : {
_id : Objectid,
firstName : String,
lastName : String,
gender : String,
email : String,
password : String
}
- Conversations ( generating unique conversation id for the user and selected category)
Conversations : {
_id : Objectid,
userId : Objectid, (Ref : Users)
category : String
}
- Messages ( saving chat messages between user and chatbot )
Messages : {
_id : Objectid,
conversationId : Objectid, (Ref : Conversations)
userId : Objectid, (Ref : Users)
type : Number (0-bot,1-user)
ratings : {
happy:Number,(1-5)
angry:Number,(1-5)
satisfied:Number,(1-5)
...
},
averageRating: Number (average of all ratings)
}
When the user is chatting with chatbot I am saving messages from both sides (chat server & user) with type and conversationId in my Messages collection.
For analyzing user experience with chatbot added a feature for admin to add ratings to that message with the given parameters means admin will rate all messages by the user about their behavior and emotion.
I created a separate API where admin can add ratings to a message with given parameters and that message will be updated with these ratings and an average rating for that message generated.
So it’s the way my application is working. Now I need to add two filters when the admin get conversations list
- admin can filter conversation by average rating (average ratings of all messages in that conversation)
- admin can filter conversation by users average rating (average ratings of all messages by user)
In both cases, ratings are in messages collection but I need to filter conversations.
How can I achieve this?