Query a MongoDB collection by applying filters using data from a separate collection

Question

Query a MongoDB collection by applying filters using data from a separate collection

Being new to MongoDB, I have created the following 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 },
});

My goal is to:

Retrieve all posts
Retrieve all muted words
Convert the muted words into corresponding regular expressions. For example,
```
{ word: "test", match: "startsWith" }
```
will be transformed into /(^|\s)test/g, and so on.
Filter out posts that match these transformed regular expressions.

How can I accomplish this task using aggregation pipelines?

javascript mongodb mongoose aggregation-framework lookup

Answer 1

Answer №1

I didn't receive a response, but I will still share the solution I discovered so it can benefit others facing a similar issue.

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

Answer 2

I didn't receive a response, but I will still share the solution I discovered so it can benefit others facing a similar issue.

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

Query a MongoDB collection by applying filters using data from a separate collection

Answer №1

Similar questions

Uncertainty regarding API endpoints in a React project

Ajax call fails to trigger upon change

Is there a way to dynamically add or modify a JavaScript timestamp component after the webpage has finished loading?

The 'save' property is not found on the 'IProtein' type. Error code: 2339

Is it advisable to utilize $locationProvider in AngularJS version 1.3.14?

Update the link by simply clicking on a div tag

Error in Bootstrap Auto-complete: Property " " cannot be read because it is undefined

Retrieve all articles in groups of 10, ensuring that each article includes the necessary details of relevant users

eliminate the common elements between two arrays in typescript/javascript

Easily transform your Twitter Bootstrap menu dropdown to appear on hover instead of click with this simple solution

What is preventing me from being able to effectively transmit the array using the POST method in Express?

Using wildcard in Angular app for MQTT observation

Tips for sending a string from the state of a React component to an external Python script

Why is the jQuery ajax file uploading feature failing to function properly in the IE9 standard mode?

The color of my Navbar remains stationary despite implementing Javascript for scroll effects

JavaScript facing issue with $.get command executing in incorrect order

The speed of Mongodb aggregation is negatively impacted if the number of search records falls below the specified limit

How do I execute raw queries with Yii's ActiveRecord when using MongoDB?

I'm interested in learning the best way to insert the images from this JSON file into the corresponding div elements

Unable to properly display the message in Angular