Unable to populate and structure document with aggregate-CodePudding

So I have this schema which have foreign keys to other collections in the database. The document has around 60k posts and each post can have multiple categories and there are around 200 categories. So I'm trying to fetch and structure data based on the category's foreign key and populate the category details and count.

Here's how the main schema and category schema looks like:

const postSchema = new mongoose.Schema( {
    post: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'PostDetails'
    },
    categories: [ {
        category: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'Category'
        },
        subCategories: [ {
            subCategory: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'Subcategory'
            }
        } ]
    } ]
} );

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

I was successful in making the count but the returned data is not what I expected. The returned data shows the id of the categories and the count but no name and slug. Here's how it looks like:

[
   {
       "_id": [
                 "617acfd232c766589c23a90c"
       ],
       "count": 876,
       "category": []
   }
]

I got the above output with the following query:

const aggregateStages = [ 
     {
          $group: {
                _id: '$categories.category',
                count: { $sum: 1 }
          }
     },
     {
          $lookup: {
                from: "Category",
                localField: "categories.category",
                foreignField: "_id",
                as: "category"
          }
     }
];

const categories = await Post.aggregate( aggregateStages ).exec();

I'm hoping to get the data as follows:

[
   {
       "_id": "617acfd232c766589c23a90c",
       "count": 876,
       "category": 'SomeCategory',
       "categorySlug": 'some-category' 
   }
]

Where am I going wrong and how can I fix it?

SAMPLE DATA FROM DB AS REQUESTED BY MATT OESTREICH

POST DATA

{
    "_id": "617adad39054bae2c983c34f",
    "post": "617ad1c80597c78ed4cc151e",
    "author": "617acc689b309fdbbbdfdfe0",
    "categories": [{
        "category": "617acfd232c766589c23a8d1",
        "subCategories":[]
    }]
}

CATEGORY DATA

{
    "_id": "617acfd232c766589c23a8d1",
    "category": "Lorem Ipsum",
    "categorySlug": "lorem-ipsum"
}

CodePudding user response：

Ok so, it looks like you can resolve this by using the $size operator. The $size operator will give you the length (or count) of elements in an array.

Live demo here

Database

db={
  "post": [
    {
      "_id": ObjectId("617adad39054bae2c983c34f"),
      "post": ObjectId("617ad1c80597c78ed4cc151e"),
      "author": ObjectId("617acc689b309fdbbbdfdfe0"),
      "categories": [
        {
          "category": ObjectId("617acfd232c766589c23a8d1"),
          "subCategories": []
        }
      ]
    }
  ],
  "categories": [
    {
      "_id": ObjectId("617acfd232c766589c23a8d1"),
      "category": "Lorem Ipsum",
      "categorySlug": "lorem-ipsum"
    }
  ]
}

Query

db.post.aggregate([
  {
    "$lookup": {
      "from": "categories",
      "localField": "categories.category",
      "foreignField": "_id",
      "as": "found_categories"
    }
  },
  {
    "$project": {
      _id: "$_id",
      count: {
        "$size": "$found_categories"
      },
      "category": {
        "$first": "$found_categories.category"
      },
      "categorySlug": {
        "$first": "$found_categories.categorySlug"
      }
    }
  }
])

Result

[
  {
    "_id": ObjectId("617adad39054bae2c983c34f"),
    "category": "Lorem Ipsum",
    "categorySlug": "lorem-ipsum",
    "count": 1
  }
]

Although, I do not believe this will give you what you are looking for if more than one category is found. Please let me know if it doesn't work and I will try to help fix it.