MongoDB group query based on input

Question

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",
    }
]

My expected output is:

 [{"product":1, "count":2},{"product":2, "count":3},{"product":3, "count":1}, 
   {"product":4, "count":1},{"product":5, "count":1}
 ]

I tried the following code, but it is not working.

 {
        $project: {
          _id: 0,
          list: { $setEquals: ["$postType", "buy"] }
        }
      }

I just inserted 4 products but it will be 10 actually.

Grouping in MongoDB usually involves the $group stage. The documentation contains a number of examples, this one in particular may be relevant for your situation. Should help you get started — user20042973
– user20042973, Commented Oct 24, 2022 at 18:48

Wernfried Domscheit · Accepted Answer · 2022-10-24 18:51:24Z

1

Would be this one:

db.collection.aggregate([
  { $match: { postType: "buy" } }, // filter on postType = "buy"
  {
    $group: {                      // group and count
      _id: "$product",
      count: { $count: {} }
    }
  },
  {
    $project: {                   // some cosmetic
      product: "$_id",
      count: 1,
      _id: 0
    }
  }
])

Mongo Playground

answered Oct 24, 2022 at 18:51

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rezaul karim Over a year ago

Thank you , let me try

Rezaul karim Over a year ago

Is there any option to populate product collection data based on the product id ? product id will be mongodb object id.

Wernfried Domscheit Over a year ago

Not clear what you mean, sorry.

Rezaul karim Over a year ago

Is it possible to count other postType in the same query? like my expected output now is

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

Wernfried Domscheit Over a year ago

No idea what you mean. Perhaps replace _id: "$product" by _id: {product: "$product", postType: "$postType"} and skip the $match. Maybe open a new question for it.

|

Collectives™ on Stack Overflow

MongoDB group query based on input

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related