2

I have two collection named as users and user_data, users is a primary collection and the user_data is a secondary collection. I have perform an aggregate query for retrieving millions of data but the query is taking too much time for execution. So, i try to use compound indexes on the fields of both the collections for e'g: audience_id: 1, contact.is_bounced: 1, contact.not_valid: 1 but on the time of execution secondary collection indexes are not use. Is there any way to use indexes on joined collection.

db.users.aggregate([
  {
    $match: {
      audience_id: {
        $in: [
          ObjectId('633eaba80cb7a3b1d910e98b'),
          ObjectId('63b6a5325bf3f04f18170e84'),
        ],
      },
      status: 'Subscribed',
    },
  },
  {
    $group: {
      _id: '$contact_id',
      contact_meta: { $first: '$$ROOT' },
    },
  },
  {
    $lookup: {
      from: 'user_data',
      let: { id: '$_id' },
      pipeline: [
        {
          $match: {
            $and: [
              {
                $expr: { $eq: ['$_id', '$$id'] },
              },
              { email: { $exists: true, $ne: '' } },
              {
                $or: [
                  { is_bounced: { $exists: false } },
                  { is_bounced: { $ne: true } },
                ],
              },
              {
                $or: [
                  { not_valid: { $exists: false } },
                  { not_valid: { $ne: true } },
                ],
              },
            ],
          },
        },
      ],
      as: 'contact',
    },
  },
  { $unwind: { path: '$contact', preserveNullAndEmptyArrays: false } },
  { $addFields: { 'contact.audience_id': '$contact_meta.audience_id' } },
  { $replaceRoot: { newRoot: '$contact' } },
]);
lpizzinidev
  • 12,741
  • 2
  • 10
  • 29

0 Answers0