I have two MongoDB collections Contact and ContactGroup. In a contact document there’s a field called groups which stores the Object IDs of a contact group (ContactGroup) in an array to easily allow querying for all contacts that belong to a specific contact group. Now the issue is with a small amount of documents in the database this modelling worked fine but as the database has scaled to over 800k documents to run a query to count all contacts that belong to a contact group is very slow which is roughly about 16-25s. What is a more optimal way to go about this?

This is the query I’m running:

const countdocs = async (query) => {
  return Contact.countDocuments(query);
};

const dt = await countdocs({
        $expr: {
          $in: [mongoose.Types.ObjectId(group._id), "$groups"]
        }
       });

Here’s the schema for Contact:

const Contact = new mongoose.Schema(
  {
    name: {
      type: String,
    },
    email: {
      type: String,
      required: true,
    },
    user: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "User",
    },
    groups: {
      type: [
        {
          type: mongoose.Schema.Types.ObjectId,
          ref: "ContactGroup",
        },
      ],
      default: [],
    },
  },
  { timestamps: true }
);

Here’s the schema for ContactGroup:

const ContactGroup = new mongoose.Schema(
  {
    title: {
      type: String,
      required: true,
    },
    description: {
      type: String,
      default: "",
    },
    verified: {
      type: Boolean,
      default: false,
    },
    user: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "User",
    },
    campaign: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "Campaign",
    },
    totalContacts: {
      type: Number,
      default: 0,
    },
  },
  { timestamps: true }
);

I’ve tried creating an index on the groups field but that also didn’t make the query more optimal.