Count elements by some fields with mongo aggregation in attribute pattern-CodePudding

I have such mongo db structure in

I need to have such result after aggregation:


{
  "RACE.VALUE": {"WHITE": 10, "ASIAN: 15},
  "ETHNICITY.VALUE": {"HISPANIC": 6, "OTHER": 13},
  ...
}

Can I do that with aggegation and optimal performance?

I understand that I need to "$unwind" it:

[
    {
        '$match': mongo_query.mongo
    },
    {
        '$project': {"KEY_DEMOGRAPHICS": 1}
    },
    {
        '$unwind': '$KEY_DEMOGRAPHICS'
    }
]

But how to group it as in example above?

CodePudding user response：

You can use the following pipeline, the trick is to use $arrayToObject after the $group stages to restructure data:

db.collection.aggregate([
  {
    $unwind: "$KEY_DEMOGRAPHICS"
  },
  {
    $group: {
      _id: {
        key: "$KEY_DEMOGRAPHICS.KEY",
        value: "$KEY_DEMOGRAPHICS.VALUE"
      },
      sum: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.key",
      values: {
        $push: {
          k: "$_id.value",
          v: "$sum"
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      values: {
        $push: {
          k: "$_id",
          v: {
            "$arrayToObject": "$values"
          }
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        "$arrayToObject": "$values"
      }
    }
  }
])

Mongo Playground