Unable to query all the documents of a reference model that match a string instead of an id-CodePudding

So I am working on an app where I have two models, Posts and Categories. In posts there's a field which references the Categories model. Now there are multiple posts with the same category and I need to fetch those posts by passing a string which will match with the category name. I need to use aggregate here as there are thousands of posts. And it takes a lot of time to fetch 90k posts and then match with the category name to find all the posts. So I want to use aggregate to lookup the category with the category name then use that category's id to find all the posts.

Suppose here are both the schemas:

**POSTS SCHEMA** 

const postSchema = new mongoose.Schema( {
    text: {
        type: String
    },
    categories: [ {
        _id: false,
        categoryID: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'Categories',
        }
    } ]
} );

**CATEGORIES SCHEMA**

const categorySchema = new mongoose.Schema( {
    category: {
        type: String,
    }
} );

So now suppose I need to find all the posts that have a category called Politics. I can't fetch all the posts and then filter out based on category name as fetching takes a lot of time because of the total number of posts. So here's what I tried:

const aggregateObj = [ 
            {
                "$lookup": {
                    "from": "categories",
                    "let": { 
                        "category": "$category"
                    },
                    "pipeline": [
                        { 
                            "$match": {
                                "category": "Politics"
                            }
                        }
                    ],
                    "as": "category"
                }
            },
            {
                $unwind: "$category"
            },
            {
                "$lookup": {
                    "from": "posts",
                    "pipeline": [
                        { 
                            "$match": {
                                "categories.categoryID": "$_id"
                            }
                        }
                    ],
                    "as": "posts"
                }
            }
];
    
const posts= await Post.aggregate( aggregateObj ).exec();

I don't get any output as it keeps on loading without any errors. What am I doing wrong here?

CodePudding user response：

You don't need to lookup another collection then use that to lookup data in another. You can populate the posts collection and then match with your desired string. Something like this:

const aggregateObj = [ 
      {
            '$lookup': {
                    'from': 'categories', 
                    'localField': 'categories.categoryID', 
                    'foreignField': '_id', 
                    'as': 'categories' // if you have a field with the same name then it will overwrite that and populate. Or you can just specify a different name
            }
      }, {
                '$match': {
                    'categories.category': 'Politics'
            }
      }
];

CodePudding user response：

What are your indices on the two collection? CategoryID should be an Object ID and indexed