Advice needed: Matching 3k records in one collection to records in another

Working with Data
, ,
1

Some advice needed on the best approach to match multiple records from one collection to records in another collection by text match.

Example use case:
I have user records in a collection, these records need to be matched to a payment record that has come from a third party data source.

I need a way to check the name of the user record against the name on the payment record, and if they match, add a property to each record.

At the moment, we are fetching all the user records for a certain user group, then making a query per user record to find a matching payment record with the following $match clause.

{
  startDate: { '$gte': '2023-03-30', '$lte': '2023-05-23' },
  groupId: { '$eq': 'group1' },
  title: { $ne': '' },
  userMatches: { '$in': [ null, false, '' ] },
  $and: [
    { $or: [{ "refId": { $eq: input.eventId } }, { "refId": { $in: [null, false, '', 0, undefined] } }] },
    {
        $or: [
            { "owner.name": { $in: input.name } },
            { "owner.contact_name": { $in: input.name } }
        ]
    }
  ]
  type: { '$ne': 'chargeback' }
}

For 3k records this can take upwards of 15 minutes to finish. The queries themselves are quite small but when you multiple that by 3k, it becomes a lot! I feel like there must be a better way to achieve this without having to loop over individual records!

In the future we plan to automate this process so the match happens when the payment data is ingested but for now I’m just looking to make this process more performant.

Any suggestions welcome!