How to compute frequency for multiple fields using a single pipeline in MongoDB?

user_12494138 0
Viewed 20

Is it possible to calculate the frequency of multiple fields with a single query in MongoDB? I can do that with separate $group stages for each field. How can I optimize it and build one pipeline that can do the job for all items?

I have the following pipeline in MongoDB 4.5

{
  $match: {
    field1: { $in: ['value1', 'value2'] },
    field2: { $in: ['v1', 'v2'] },
  }
},
{
  $group: {
    _id: {
      field1: '$field1',
      field2: '$field2'
    },
    frequency: { $sum: 1.0 }
  }
}

From this, I obtain data like the following:

{ 
  "_id": {
      "field1": "value1", 
      "field2": "v1"
  }, 
  "count": 7.0
},
{ 
  "_id": {
      "field1": "value1", 
      "field2": "v2"
  }, 
  "count": 3.0
},
{ 
  "_id": {
      "field1": "value2", 
      "field2": "v1"
  }, 
  "count": 4.0
}

The result that I am trying to get is:

{
  "field1": [
    "value1": 10.0,
    "value2": 4.0
  ],
  "field2": [
    "v1": 11.0,
    "v2": 3.0
  ]
}
aggregation-framework mongodb-query mongodb
1 Answers
  • convert your required fields into array key-value format using $objectToArray
  • $unwind to deconstruct the above converted array
  • $group by key and value and count sum
  • $group by key and construct the array of value and count
  • $group by null and construct the array of field and above array after converting from $arrayToObject
  • $replaceToRoot to replace above array after converting from array to object
db.collection.aggregate([
  {
    $match: {
      field1: { $in: ["value1", "value2"] },
      field2: { $in: ["v1", "v2"] }
    }
  },
  {
    $project: {
      arr: {
        $objectToArray: {
          fields1: "$field1",
          fields2: "$field2"
        }
      }
    }
  },
  { $unwind: "$arr" },
  {
    $group: {
      _id: {
        k: "$arr.k",
        v: "$arr.v"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.k",
      arr: {
        $push: {
          k: "$_id.v",
          v: "$count"
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      arr: {
        $push: {
          k: "$_id",
          v: { $arrayToObject: "$arr" }
        }
      }
    }
  },
  { $replaceRoot: { newRoot: { $arrayToObject: "$arr" } } }
])

Playground

user_89871280
Related