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