Filter MongoDB collection using values from another collection

user_930925 0
Viewed 75

I am a MongoDB beginner. I have these schemas:

const postSchema = new mongoose.Schema(
    {
        content: { type: String, required: true, index: "text" },
        author: { type: ObjectId, ref: "User", required: true, index: true }
    }
);

const muteWordSchema = new mongoose.Schema({
    word: { type: String, trim: true, required: true },
    match: { type: String, enum: ["exact", "contains", "startsWith", "endsWith"], required: true },
});

What I want to do is:

  1. Fetch all posts
  2. Fetch all muted words
  3. Transform the muted words into corresponding regular expressions. For example, { word: "test", match: "startsWith" } will become /(^|\s)test/g, and so on.
  4. Filter out all posts that match these transformed regular expressions.

How can I achieve this using aggregation pipelines?

mongoose aggregation-framework mongodb lookup javascript
1 Answers

I didn't get an answer, but I will post the solution I found anyway, so it will be useful for anyone else having a similar problem.

db.posts.aggregate([
    {
        $lookup: {
            from: "mutedwords",
            pipeline: [
                {
                    $project: {
                        _id: 0,
                        regEx: {
                            $switch: {
                                branches: [
                                    {
                                        case: {
                                            $eq: [ "$match", "startsWith" ]
                                        },
                                        then: {
                                            $concat: [
                                                "\\W+",
                                                "$word",
                                                ".*"
                                            ]
                                        }
                                    },
                                    {
                                        case: {
                                            $eq: [ "$match", "endsWith" ]
                                        },
                                        then: {
                                            $concat: [
                                                "\w*",
                                                "$word",
                                                "(\\W+|$)"
                                            ]
                                        }
                                    },
                                    {
                                        case: {
                                            $eq: [ "$match", "exact" ]
                                        },
                                        then: {
                                            $concat: [
                                                "\\W+",
                                                "$word",
                                                "(\\W+|$)"
                                            ]
                                        }
                                    }
                                ],
                                default: "$word"
                            }
                        }
                    }
                },
                {
                    $group: {
                        _id: undefined,
                        result: {
                            $addToSet: "$regEx"
                        }
                    }
                }
            ],
            as: "mutedWords"
        }
    },
    {
        $addFields: {
            mutedWords: {
                $arrayElemAt: ["$mutedWords.result", 0]
            }
        }
    },
    {
        $match: {
            $expr: {
                $eq: [
                    {
                        $filter: {
                            input: "$mutedWords",
                            cond: {
                                $regexMatch: {
                                    input: "$content",
                                    regex: "$$this",
                                    options: "i"
                                }
                            }
                        }
                    },
                    []
                ]
            }
        }
    },
    {
        $unset: "mutedWords"
    }
]);
user_9309250
Related