How to count the amount of clicks?

Working with Data
,
1

Hello everyone! I want to create a table filled with the stores in my db. In the row I also want to display the total amount of clicks. The clicks are however on the deals collection. How can i solve this? :slight_smile:

store example: 
{
    "_id": {
      "$oid": "639449e63234220ad3e22642"
    },
    "name": "Tesla",
    "slug": "tesla"
  }

deals example (will be many different deals document for the store): {
    "_id": {
      "$oid": "63944bb38ac849ba16eb01d3"
    },
    "description": "Save 11%",
    "store": {
      "$oid": "639449e63234220ad3e22642"
    },
    "clicks": 10
  }

Expected end results:

{
    "_id": {
      "$oid": "639449e63234220ad3e22642"
    },
    "name": "Tesla",
    "slug": "tesla",
   "clicks": 5822
  }
2

You simply do the same $lookup as you do in Help with aggregate store and its deals but with a $group stage _id:null that $sum the clicks.

1 Like
3

@steevej

You mean this?

db.stores.aggregate([{$lookup: {from: "deals", localField: "_id", foreignField: "store", as: "deals"}}])

I gave it a try but I dont understand where ur code would go here? :thinking: :slightly_frowning_face:

4

A pipeline that uses $group with _id:null that $sum the clicks would look like:

count_clicks = [ { "$group" : {
    "_id" : null ,
    "clicks" : { "$sum" : "$clicks" }
} } ]

Then you simply put this in your lookup as:

db.stores.aggregate([{$lookup: {from: "deals", localField: "_id", foreignField: "store", as: "deals", pipeline:count_clicks}}])

The format of the final clicks field will look like:

"clicks" : [ { _id : null , "clicks" :  5822 } ]

Then a cosmetic $set stage can easily transform the above to what you wish.

Use my code above at your own risk.

1 Like
5

Thanks for this! It did work but seems like it added some unecessary stuff in there? I ended up asking Co-pilot and it actually managed to give me exactly what i wanted:

db.stores.aggregate([
  {
    $lookup: {
      from: "deals",
      localField: "_id",
      foreignField: "store",
      as: "deals"
    }
  },
  {
    $project: {
      name: 1,
      slug: 1,
      clicks: {
        $sum: "$deals.clicks"
      }
    }
  }
])

But very grateful for your help, thank you!

1 Like
6

Yep, like I mentioned

Notes that your version will use more memory, since all the deals are kept from the $lookup until your final $project. By counting, the clicks in the pipeline of the $lookup, just the count is kept. Keeping all the deals inside the store object until the final $project increases the chances to get the 16Mb limit on object size.

The following can simply be used in your final $project to remove the extra stuff.

clicks : { $arrayElemAt : [ "$clicks.clicks"  , 0 ] }

The whole pipeline would look like:

db.stores.aggregate([
  {
    $lookup: {
      from: "deals",
      localField: "_id",
      foreignField: "store",
      as: "deals",
      pipeline: [ { "$group" : {
          "_id" : null ,
          "clicks" : { "$sum" : "$clicks" }
      } } ]
    }
  },
  {
    $project: {
      name: 1,
      slug: 1,
      clicks : {
          $arrayElemAt : [ "$clicks.clicks"  , 0 ]
      }
    }
  }
])
1 Like
7

All this discussion made me thinks that there might be a better way to do that.

The idea is to aggregate the deals first and to a $lookup for the store.

Something along the untested lines:

group_stage = { "$group ": {
    "_id" : "$store" ,
    "clicks" : { "$sum" : "$clicks" }
} }

lookup_stage = { "$lookup" : {
    "from" : "stores" ,
    "localField" : "store" ,
    "foreignField" : "_id" ,
    "as" : "store" 
} }

project_stage = { "$project" : {
    "name" : { $arrayElemAt : [ "$store.name"  , 0 ] } ,
    "slug" : { $arrayElemAt : [ "$store.slug"  , 0 ] }
    "clicks" : 1
} }

pipeline = [ group_stage , lookup_stage , project_stage ]

It could be fun to see which one perform better. I sure do not know.

1 Like