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*

Hi, it seems like you don't have any option. You need minimum MongoDB version 4.0 to achieve what you want. Check here: https://stackoverflow.com/questions/53953664/mongodb-3-6-how-to-convert-string-to-object-id — Dheemanth Bhat, Mar 10 '21 at 14:34
I unfortunately don't have that option...I am limited by the version used in my work... — chimera_girl, Mar 10 '21 at 16:15

Dheemanth Bhat · Answer 1 · 2021-03-08T18:56:46.013

1

Convert filename to ObjectId.
Perform $lookup (or join) operation using filename and _id.
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]
            }
        }
    }
])

edited Mar 08 '21 at 18:56

answered Mar 08 '21 at 12:54

Dheemanth Bhat

4,269
2
21
40

hi, thanks for the prompt answer, but this doesnt seem to work. im getting this error E QUERY [js] SyntaxError: missing ] after element list @(shell):1:195 – chimera_girl Mar 08 '21 at 13:36
Where are u getting this error? inside Node.js(Express.js) or in terminal or an DB tool that u r using? – Dheemanth Bhat Mar 08 '21 at 13:49
im running this in terminal. i tried running it in a file and get "A pipeline stage specification object must contain exactly one field.", – chimera_girl Mar 08 '21 at 14:11
MongoDB shell version v3.6.13 – chimera_girl Mar 08 '21 at 14:35

score 0 · Accepted Answer · answered Mar 15 '21 at 09:42

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]
            }
        }
    }
])

Manipulating data and join mongodb

2 Answers2