Querying whole collection again

Hi all,

I have a collection where the key field is

{acc_id:'A23BCXY',
acc_open_date: '2022-05-21',
acc_close_date:'9999-12-31'}

once the account is closed acc_id is getting appended with acc open date(not with close date) and there is flag: 'N' added when the account is closed.
The above account once closed will look like this:

{acc_id:'A23BCXY_20220521',
acc_open_date:'2022-05-21',
acc_close_date:'2022-6-30',
flag:'N'}
}

I want to find all closed accounts and their corresponding open account if any (consider case of a recycled account or maybe after the account is closed and opened again by mistake)

Now I want to loop through all closed accounts {flag:'N'}, when I will get acc_id, will use substring to remove appended dates (A23BCXY_20220521->A23BCXY). Now I will have only acc_id without an appended date.
How I can use this acc_id to loop the whole collection again and check if an open account still exists?

Hello @College_days_N_A ,

I notice you haven’t had a response to this topic yet - were you able to find a solution?
If not, then there are a couple of things that I may need clarification on:

  1. Is acc_id supposed to be unique?
  2. If I understand correctly, when an account is closed, two things happened:
  • flag:N
  • acc_id_<closed_date> mutation.
    Is this accurate?

I believe the mutation is problematic, since it’s adding redundancy, making it very hard to check for existing acc_id, and you may get duplicate acc_id which is not ideal if it’s supposed to be unique.

Have you considered:

  1. You can just use _id in place of acc_id or make acc_id uniquely indexed. This may solve the reopened account issue
  2. Use the flag:N to determine that an account is closed, instead of using it and mutating the acc_id
  3. The account open date is already in the document, so there’s no need to add this information again at the expense of making duplicate acc_id a possibility

In case you cannot improve your schema, the query below might be helpful to achieve your goal.
Note that this is untested so you might want to do your own testing to ensure it works with your data.

db.test.aggregate([
    {$addFields:{
        id:{$regexFind:{
            input:'$acc_id',
            regex:'[^_]+'
        }}
    }},
    {$group:{
        _id:'$id.match',
        acc_id:{$push:'$acc_id'},
        count:{$count:{}}
    }}
])

However I would lean toward modifying the workflow to make the data easier to work with in the future.

Regards,
Tarun