MongoDB Generate Group query based on the keyword-CodePudding

I am trying to create top 10 product list based on postType = "buy". My logic is a count postType = "buy" and sort the top 10 products from the logs collection. Here are my sample log collections.

[
    {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "3",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "view",
        "product": "4",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "view",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "share",
        "product": "3",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "share",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "1",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "1",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "viewvideo",
        "product": "1",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "viewvideo",
        "product": "2",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "viewvideo",
        "product": "3",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "4",
    },
 {
        "_id": "633dc5b761ff04e7ae8e8c0f",
        "postType": "buy",
        "product": "5",
    }
]

I am trying to get count for totalBuybutton, totalShareButton, totalView if 'buy' keyword matched. My expected output is:

[
   {"product":1, "totalBuycount":2, "shareButtonCount":4, viewCount":4},
   {"product":2, "totalBuycount":3, shareButtonCount":4, viewCount":4},
   {"product":3, "totalBuycount":1, shareButtonCount":4, viewCount":4},     
   {"product":4, "totalBuycount":1, shareButtonCount":4, viewCount":4},
   {"product":5, "totalBuycount":1, shareButtonCount":2, viewCount":4}
]

My current implementation is as below

aggregate([
      {
        $match: {
          postType: "buybutton"
        },

      },
      {
        $group: {
          _id: "$product",
          count: {
            $count: {}
          }
        }
      },
      {
        $project: {
          product: "$_id",
          count: 1,
          _id: 0,
        },

      },)

CodePudding user response：

No idea, if this is what you are looking for.

The main part is

{
  $group: {
     _id: { postType: "$postType", product: "$product" },
     count: { $count: {} }
  }
}

Do you really need exactly the output as given in the question. It would require quite a lot of cosmetic work

db.collection.aggregate([
   {
      $group: {
         _id: { postType: "$postType", product: "$product" },
         count: { $count: {} }
      }
   },
   {
      $group: {
         _id: "$_id.product",
         data: { $push: "$$ROOT" }
      }
   },
   {
      $project: {
         _id: 0,
         product: "$_id",
         data: {
            $arrayToObject: {
               $map: {
                  input: "$data", in: {
                     k: "$$this._id.postType", v: "$$this.count",
                  }
               }
            }
         }
      }
   },
   { $replaceWith: { $mergeObjects: ["$$ROOT", "$data"] } },
   { $unset: "data" }
])


]
  { product: '5', buy: 1 },
  { product: '3', buy: 1, share: 1, viewvideo: 1 },
  { product: '2', buy: 3, view: 1, viewvideo: 1, share: 1 },
  { product: '1', viewvideo: 1, buy: 2 },
  { product: '4', buy: 1, view: 1 }
]

Mongo Playground