CODEWITHSUNDEEP
mongodbgroup-bycountaggregation-frameworkaggregation
InfinitePrime
InfinitePrime

Reputation: 1776

Obtain count of fields from grouped rows

Data:

[
  { created_at: "2020-05-08T18:46:08.020Z", view: true, cta: false },
  { created_at: "2020-05-08T19:00:00.020Z", view: true, cta: true },
  { created_at: "2020-05-08T19:30:45.020Z", view: true, cta: false },
  { created_at: "2020-05-08T19:55:50.020Z", view: true, cta: true }
]

When I group the data on hourly interval via created_at I get the count of unique created_at just fine.

  $group: {
    $hour: { hour: '$created_at' },
    count: { $sum: 1 }

  }

Result:

   hour: 18, count: 1,
   hour: 19, count: 3

Now, how do I also include the count of cta and view field, when the field value is true. I would like to obtain result like:

    [{ hour: 18, ctaCount: 0, viewCount: 1 }],
    [{ hour: 19, ctaCount: 2, viewCount: 3 }]

Tried grouping with multiple group operators and it does not work as expected.

Upvotes: 1

Views: 28

Answers (1)

mickl
mickl

Reputation: 49945

You can use $cond and refer to cta field directly since it's bool:

db.collection.aggregate([
    {
        $group: {
            _id: { hour: { $hour: "$created_at" } },
            count: { $sum: 1 },
            ctaCount: { $sum: { $cond: [ "$cta", 1, 0 ] } }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions