How to get distinct values in an array and group by them in MongoDB

43 Views Asked by At

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
      }
    }
  }
])
0

There are 0 best solutions below