MongoDB - Get a summary of the count for each field for each value of a collection

user_10813252 0
Viewed 111

I would like to get a summary of the count for each field for each value of a collection.

Example:

A collection may contain the following document schema and values:

{
    shop: A, B, C, D
    fruits: banana, apple, kiwi
    region: north, south, west, east
}

Goal: I want to get a single document as a result with the count for every value like this:

{
    shop: {
        A: 2,
        B: 5,
        C: 3,
        D: 1
    },
    fruits: {
        banana: 3,
        apple: 2,
        kiwi: 1
    }
    ...
}

How is that possible with mongo?

Thanks for your help!

aggregation-framework pymongo mongodb
1 Answers
  • $facet to separate result for all three fields
  • $project shop field only
  • $unwind deconstruct shop array
  • $group by shop and get count
  • $project to show only key as shop and value as count
  • same process both other 2 arrays fruits and region
  • $arrayToObject convert key-value array to object format
db.collection.aggregate([
  {
    $facet: {
      shop: [
        { $project: { shop: 1 } },
        { $unwind: "$shop" },
        { $group: { _id: "$shop", count: { $sum: 1 } } },
        { $project: { _id: 0, k: "$_id", v: "$count" } }
      ],
      fruits: [
        { $project: { fruits: 1 } },
        { $unwind: "$fruits" },
        { $group: { _id: "$fruits", count: { $sum: 1 } } },
        { $project: { _id: 0, k: "$_id", v: "$count" } }
      ],
      region: [
        { $project: { region: 1 } },
        { $unwind: "$region" },
        { $group: { _id: "$region", count: { $sum: 1 } } },
        { $project: { _id: 0, k: "$_id", v: "$count" } }
      ]
    }
  },
  {
    $project: {
      shop: { $arrayToObject: "$shop" },
      fruits: { $arrayToObject: "$fruits" },
      region: { $arrayToObject: "$region" }
    }
  }
])

Playground

user_89871280
Related