Setting a field in CosmosDB using MongoDB API by searching an external array of results from another database

72 Views Asked by At

Please note for the purposes of this question, I'm stuck on Mongo API v4.2.

I've got a collection (validity) in one MongoDB database (AdminInfo) with an external ID reference field called licensingProfileId. Using only that value, I need to check whether an identification number given by the user is valid or not based on the user's last name. The problem is, neither the identification number nor the last name are currently stored anywhere in AdminInfo, and they're not even stored in the same database as each other.

Also, our databases are running on Azure CosmosDB using the MongoDB API, so it seems I'm unable to do a simple lookup across databases. I did try, but either due to our Mongo API version (4.2) or the simple fact that we're on Azure, I got a pretty definitive error. Either way, here's how I tried that, for good measure:

db.getCollection('validity').aggregate([
    {
        $lookup: {
            from: { db: 'Licensing', coll: 'licensingProfiles' },
            localField: 'licensingProfileId',
            foreignField: '_id',
            as: 'licensingProfile'
        }
    }
]);

// error 40321: $lookup argument 'from' must be a string, is type Document

Given all these constraints, I decided to try using the $function aggregation stage, but honestly I can't tell if the pre-formed arrays from the other databases are even in scope for the function. Here's what I tried to do in the mongo shell:

const run = async () => {
    const licensingProfiles = await db.getSiblingDB('Licensing').licensingProfiles.find({}).toArray();
    const userProfiles = await db.getSiblingDB('CustomerEnrollment').userProfiles.find({}).toArray();

    db.getCollection('validity').aggregate([
        {
            $match: {
                licensingProfileId: '[insert hex id here]'
                // this match stage won't be here at all in the final version of the query.
                // eventually I need to update all of the validity entries this way, but the match is just for testing on a smaller scale.
            }
        },
        {
            $set: {
                'identificationNumber.isValid': {
                    $function: {
                        body: function(lpId) {
                            const licPrf = licensingProfiles.find(prf => prf._id === lpId);
                            const usrPrf = userProfiles.find(prf => prf.licensingProfileId === lpId);
                            if (!licPrf || !usrPrf) {
                                return false;
                            }

                            const lastName = licPrf.lastName;
                            const idNum = usrPrf.accountInfo?.idNum;
                            if (!lastName || !idNum || idNum.length !== 9 || idNum[1] !== lastName[0]) {
                                return false;
                            }

                            return true;
                        },
                        args: [ '$licensingProfileId' ],
                        lang: 'js'
                    }
                }
            }
        }
    ]);
};

run();

I also tried putting a return in front of the db.getCollection(...).aggregate(...) call inside of the run async method, and that finally gave me an unknown operator: $function error, at which point I realized that $function is new as of MongoDB 4.4 and I wouldn't have access to it. I also know that this would be super slow to run even if it worked, but it's all I've been able to come up with.

Is there any way to do this on CosmosDB using MongoDB 4.2, or is my only course of action to either upgrade the server version and all of our drivers or completely redesign/restructure the database?

0

There are 0 best solutions below