Project a counter of occurrences from document array

Hello there!

I need help in counting string occurences inside an array and project them inside my data aggregation.

The aggregation has a couple of aggregation steps but after the last $project stage the documents look like this:

{
  id: 'Dynamic Ad Campaign',
  requestcount: 50,
  bookingcount: 5,
  flags: ['a', 'bk', 'yz', 'a', 'a', 'bk'],
  regions: [
    ObjectId("606467245ed47a479bac6df7"),
    ObjectId("606467245ed47a479bac6df7"),
    ObjectId("606467245ed47a479bac6df8"),
    ObjectId("606467245ed47a479bac6df9")
  ]
}

And what I want is counting the occurences of the entries inside flags and reqs and project this as a new field. So the output would look like this:

{
  id: 'Dynamic Ad Campaign',
  requestcount: 50,
  bookingcount: 5,
  flags: [
    { name: 'a', count: 3 },
    { name: 'bk', count: 2 },
    { name: 'yz', count: 1 }
  ],
  regions: [
    {
      _id: ObjectId("606467245ed47a479bac6df7"),
      count: 2
    },
    {
      _id: ObjectId("606467245ed47a479bac6df8"),
      count: 1
    },
    {
      _id: ObjectId("606467245ed47a479bac6df9"),
      count: 1
    }
  ]
}

I don’t really have a clue how I can do this during aggregation. It’s easy to achieve this in node with the array returned from the aggregation, but I would prefer doing this inside the query itself. Any input?

Click here for the full aggregation pipeline
db.getCollection('campaigns').aggregate([
  {
    $addFields: {
      flags: '$bookings.flags',
      regions: '$requests.region',
      requestcount: { '$size': '$requests' },
      bookingcount: { '$size': '$bookings' },
    }
  },
  {
    $project: {
      id: 1,
      name: 1,
      requestcount: 1,
      bookingcount: 1,
      regions: 1,
      flags: {
        '$reduce': {
          input: '$flags',
          initialValue: [],
          in: { '$concatArrays': [ '$$value', '$$this' ] }
        }
      },
    }
  }
])

Cheers,
George

Hello @Georg_Bote, I have this aggregation which outputs the desired result.

db.collection.aggregate([ 
{ 
  $unwind: "$flags" 
}, 
{ 
  $group: { 
      _id: "$flags", 
      count: { $sum: 1 }, 
      regions: { $first: "$regions" }, 
      doc: { $first: { id: "$id", requestcount: "$requestcount", bookingcount: "$bookingcount" } }  
}}, 
{ 
  $group: { 
      _id: null, 
      flags: { $push: { name: "$_id", count: "$count" }}, 
      regions: { $first: "$regions" }, doc: { $first: "$doc" } 
}},
{ 
  $unwind: "$regions" 
},
{ 
  $group: { 
      _id: "$regions", 
      count: { $sum: 1 }, 
      flags: { $first: "$flags" }, 
      doc: { $first: "$doc" } 
}}, 
{ 
  $group: { 
      _id: null, 
      regions: { $push: { _id: "$_id", count: "$count" }}, 
      flags: { $first: "$flags" }, 
      doc: { $first: "$doc" } 
}},
{ 
  $project: { 
      _id: 0, 
      id: "$doc.id", 
      requestcount: "$doc.requestcount", 
      bookingcount: "$doc.bookingcount",  
      regions: 1, 
      flags: 1 
}}
])

Note that it is possible that your aggregation’s $project is not required.

2 Likes

Thank you @Prasad_Saya ! This worked very well!

This is NOT a good way - you should never use $unwind and then $group when you just need to transform a single document!

If you have an array flags: [ 'a', 'b', 'x', 'y', ... ] the way to get number of unique entries is like this:

{$addFields:{
    flags: {$map: {
         input: {$setUnion:"$flags"},  /* unique elements of flags array */
         as: "f", 
         in: {
              name: "$$f",
              count: {$size: {$filter:{input:"$flags", cond:{$eq:["$$this", "$$f"]}}}}
         }
    }}
}}

Asya

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.