Query maximum N records of each group base on a condition in MongoDB?

Question

I have a question regarding querying data in MongoDB. Here is my sample data:

{
    "_id": 1,
    "category": "fruit",
    "userId": 1,
    "name": "Banana"
},
{
    "_id": 2,
    "category": "fruit",
    "userId": 2,
    "name": "Apple"
},
{
    "_id": 3,
    "category": "fresh-food",
    "userId": 1,
    "name": "Fish"
},
{
    "_id": 4,
    "category": "fresh-food",
    "userId": 2,
    "name": "Shrimp"
},
{
    "_id": 5,
    "category": "vegetable",
    "userId": 1,
    "name": "Salad"
},
{
    "_id": 6,
    "category": "vegetable",
    "userId": 2,
    "name": "carrot"
}

The requirements:

1. If the category is fruit, returns all the records match
2. If the category is NOT fruit, returns maximum 10 records of each category grouped by user
3. The category is known and stable, so we can hard-coded in our query.

I want to get it done in a single query. So the result expected should be:

{
    "fruit": [
        ... // All records of 
    ],
    "fresh-food": [
        {
            "userId": 1,
            "data": [
            // Top 10 records of user 1 with category = "fresh-food"
            ]
        },
        {
            "userId": 2,
            "data": [
            // Top 10 records of user 2 with category = "fresh-food"
            ]
        },
        ...
    ],
    "vegetable": [
    {
            "userId": 1,
            "data": [
            // Top 10 records of user 1 with category = "vegetable"
            ]
        },
        {
            "userId": 2,
            "data": [
            // Top 10 records of user 2 with category = "vegetable"
            ]
        },
    ]   
}

I've found the guideline to group by each group using $group and $slice, but I can't apply the requirement number #1.

Any help would be appreciated.

Answer 1

You need to use aggregation for this

• $facet to categorize incoming data, we categorized into two. 1. Fruit and 2. non_fruit
• $match to match the condition
• $group first group to group the data based on category and user. Second group to group by its category only
• $objectToArray to make the object into key value pair
• $replaceRoot to make the non_fruit to root with fruit

Here is the code

db.collection.aggregate([
  {
    "$facet": {
      "fruit": [
        { $match: { "category": "fruit"  } }
      ],
      "non_fruit": [
        {
          $match: {
            $expr: {
              $ne: [ "$category", "fruit" ]
            }
          }
        },
        {
          $group: {
            _id: { c: "$category", u: "$userId" },
            data: { $push: "$$ROOT" }
          }
        },
        {
          $group: {
            _id: "$_id.c",
            v: {
              $push: {
                uerId: "$_id.u",
                data: { "$slice": [ "$data", 3 ] }
              }
            }
          }
        },
        { $addFields: {  "k": "$_id", _id: "$$REMOVE" } }        
      ]
    }
  },
  { $addFields: { non_fruit: { "$arrayToObject": "$non_fruit" } }},
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [ "$$ROOT", "$non_fruit" ]
      }
    }
  },
  { $project: { non_fruit: 0 } }
])

Working Mongo playground