Manipulating data and join mongodb

Question

I am relatively new to Mongodb, and I need to do the following:

I have 2 tables, "records" and "files".

A row in the "records" table looks like this: { "_id" : ObjectId("6012d31aea8fb06d8bf438a0"), "img" : false, "type" : "DRAFT", "submitted" : true }

And a row in the "files" table looks like this: { "_id" : ObjectId("5fabf23f917863623ec54a86"), "filename" : "6012d31aea8fb06d8bf438a0", "uploadDate" : ISODate("2020-11-11T14:16:31.462Z"), "length" : NumberLong(4119) }

The field "filename" in the "files" table corresponds to the _id field in the "records" table.

How can I find all "files" whose filename is not an id in the "records" table?

Thanks!

Edit: * I am using Mongo version 3.6*

Answer 1

Mongo 3.6 version:

I created a collection "temp" (that I will later delete to save space):

db.createCollection("temp");

    db.records.find().map( function(u) { 
    db.temp.insert({'strId': u._id.str, 'type': u.type, 'status': u.submitted, 'image': u.img});
});

Then queried it using a similar aggregation to @Dheemanth Bhat's answer:

db.temp.aggregate([
    {
        $lookup: {
            from: "files",
            let: { testId:  "$strId"  },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$$testId", "$filename"] }
                    }
                }
            ],
            as: "corresponding_id"
        }
    },
    {
        $match: {
            $expr: {
                $eq: [{ $size: "$corresponding_id" }, 0]
            }
        }
    }
])

Answer 2

1. Convert filename to ObjectId.
2. Perform $lookup (or join) operation using filename and _id.
3. Retain empty records (which means records collection has no entry for that file).

Try this query:

db.files.aggregate([
    {
        $lookup: {
            from: "records",
            let: { file_id: { $toObjectId: "$filename" } },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$file_id"] }
                    }
                }
            ],
            as: "records"
        }
    },
    {
        $match: {
            $expr: {
                $eq: [{ $size: "$records" }, 0]
            }
        }
    }
])