MongoDB - Find records in one collection that match string in 2nd collection

Question

I have a MongoDB collection of product IDs/unique products attributes and a second collection of codes that relate to attributes common for products whose IDs are prefixed with those codes.

For example, two product IDs might be aaa17859 and aaa98542 so their code prefix is aaa.

I have several thousand codes, and the codes rarely change, so the 'codes' collection is more of a data repo. The products change frequently throughout the day, and imports can run from several hundred to a few thousand.

Currently, I am running the below to join the two sets together when the data is first loaded in.

import re

code_collection= list(db.codes.find())
for x in code_collection:   
    p = re.compile(x['CODE-PREFIX'] + '.*')
    db.productdataset.update({'productID': {'$regex': p}}, { '$set': {'codeData': x['data_property']}}, multi=True )

Pro to this method is that it reduces the number of calls I would otherwise have to make once the data is initially loaded.

Major con is that when the code collection is updated (say, a new common attribute), something similar has to be rerun against the entire product collection. Plus, depending on this list, it can take a little longer than I'd like to load in the data. Also, this means I can end up with a good deal of duplication. In general, this approach just seems a little kludgy.

What I would like to optimize is a method to join these two sets together in one call, thus preventing the rerun. I have been looking at a lot of mapreduce examples, but have had trouble figuring out how I would be able to use the prefix from the code collection to use regex and look up it's related productID.

Answer 1

You're turning the result of db.codes.find() into a list, but you're just iterating over it anyway. Why not just leave it in whatever form it arrives? If it can be converted to a list it can probably be iterated over. This would save you the time of conversion and mean you might not need to have a full list stored in memory.

Also x isn't a great name for the temporary value when you could just use code and take up only 3 more letters.