Group By Specific Column within a Collection

[Rajavel_Selvaraj_Ganesan]

Here is my sample Collection data :

{
“_id”: “63f52a5240c6bf8fa4dd9a79”,
“Protection_Level”: “1”,
“Stock_Count”: “10”,
“Product_ID”: “11468752”,
“LAST_UPDATED_DTTM”: “20230216134424”,
“Store_ID”: “100”,
“ABC_Protection_Level”: “4”
}

I got like these 1.7Million records with product/store and its stock. How would I create aggregation as below ?

• Stock count by store for each products (or)
• Bucket them into chunks to process the collection data easily for posting via API ?

I am exploring $Group / $bucket / $bucketauto - couldn’t find easy way to write the pipeline according to my need. Any help would be appreciated!

[steevej (Steeve Juneau)]

Please share what you tried so far. This way we avoid losing time working on a solution you already know it is not working. This could also save us by simply adapting or improving on a partially working solution.

@Rajavel_Selvaraj_Ganesan

Stock count by store for each products

Please share the sample result you would like.

@Rajavel_Selvaraj_Ganesan

Bucket them into chunks to process the collection data easily for posting via API ?

The above requirement is not clear enough to propose anything meaningful.

[Rajavel_Selvaraj_Ganesan]

I got 3 Million records within the collection.

• 10K Unique products

• 200+ Stores

I was thinking to create aggregation pipeline as below to utilize within my springboot API to post into a vendor api.

• Stock/Inventory count for each product across all the stores (so, it will be 10K API Calls instead of 3M API Calls) [OR]

• Bucket them into chunks to process the collection data easily for posting into vendor api from the pipeline

Here are the options I am trying :

//Mongo Community
//Group Inventory data
{
$group: {
“_id”: {
Product_id: “$Product_ID”,
Store_id: “$Store_ID”,
Stock_cnt : “$Stock_Count”
}
}
}

// Bucket Auto
//Bucket Auto Inventory Example ; I couldn’t think through how to then create subsequent stages

db.artwork.aggregate( [
{
$bucketAuto: {
groupBy: “$Product_ID”,
buckets: 10
}
}

] )

//Another Bucket approach

//Bucket Stock Data ; But it defaults them into “OTHER” as overall SUM INVENTORY DATA

db.artists.aggregate( [
// First Stage
{
$bucket: {
groupBy: “$Stock_Count”, // Field to group by
boundaries: [ 10, 25, 35, 45, 55, 80, 100 ], // Boundaries for the buckets
default: “Other”, // Bucket ID for documents which do not fall into a bucket
output: { // Output for each bucket
“count”: { $sum: 1 },
“artists” :
{
$push: {
“Product_ID”: $Product_ID ,
“Store_ID”: $Store_ID ,
“Stock_Count”: “$Stock_Count”
}
}
}
}
}

Please let me know if this helps.

[Rajavel_Selvaraj_Ganesan]

I was able to find the solution by myself - thanks to MongoDB University videos & Stackoverflow

MongoDB nested group?

MongoDB Courses and Trainings | MongoDB University

Sharing the working code below for anyone coming across similar scenario to achieve it.

//Getting Products and its stores + inventory count using nested group scenario

[
  {
    $group:
      /**
       * _id: The id of the group.
       * fieldN: The first field name.
       */
      {
        _id: {
          Product_ID: "$Product_ID",
          Store_ID: "$Store_ID",
          Stock_Count: "$Stock_Count",
        },
      },
  },
  {
    $group:
      /**
       * _id: The id of the group.
       * fieldN: The first field name.
       */
      {
        _id: "$_id.Product_ID",
        Subset: {
          $push: {
            Store_ID: "$_id.Store_ID",
            Stock_Count:
              "$_id.Stock_Count",
          },
        },
      },
  },
]