How to filter documents with querying other collection in mongoDB (mongoose)

Working with Data
1

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?