I have data as below, and I want to get the distinct values from array and count their occurance.
Data :
[
{
"key": "Dummy",
"country_array": [
"USA",
"UK",
"Italy"
]
},
{
"key": "Dummy",
"country_array": [
"USA",
"Italy"
]
},
{
"key": "Dummy2",
"country_array": [
"USA"
]
}
]
I want to have below output :
[
{
"country": "USA",
"total": 2
},
{
"country": "Italy",
"total": 2
},
{
"country": "UK",
"total": 1
}
]
I tried using below aggregate query, but I am not able to get the distinct values and correct result. Any suggestions appreciated. Thanks...
db.collection.aggregate([
{
$match: {
"key": "Dummy"
}
},
{
$group: {
_id: {
"country_array": "$country_array"
},
count: {
$sum: 1
}
}
}
])