MondoDb aggregate for all key in nested field

user_9135982 0
Viewed 35

I have a collection as:

{ 
   userId: ObjectId("..."),
   action: {
             type: "predefined value",
             key1: "key1Value",
             key2: "key2Value",
             key3: "key3Value"
           }
},{ 
   userId: ObjectId("..."),
   action: {
             type: "predefined value",
             key1: "key1aValue"
           }
},
{ 
   userId: ObjectId("..."),
   action: {
             type: "predefined value",
             key3: "key3Value",
             key4: "key4Value"
           }
}

By aggregating to the first stage I make a match for the requested userId and I can count the values ​​of a specific key.

[
  {
    '$match': {
      'userId': new ObjectId('62e3aa7cd4524678b541f4fc')
    }
  }, {
    '$group': {
      '_id': '$action.key1', 
      'count': {
        '$sum': 1
      }
    }
  }
]

This way I get this ouput

[
  {_id: "key1Value", count: 1},
  {_id: "key1aValue", count: 1}
]

How can I get through a single aggregate the count of values ​​for each single key as in the output below?

[
  key1: [
         {_id: "key1Value", count: 1},
         {_id: "key1aValue", count: 1}
        ],
  key2: [
         ...
        ],
  key3: [
         ...
        ],
  ....
]
mongoose node.js mongodb backend aggregate
1 Answers

You need to do some structure manipulation to make this happens, here is an approach using $objectToArray to convert the actions object to an array, at that point we can $unwind it and $group accordingly.

Your requested output is not a valid json so I had to guesstimate what you meant:

db.collection.aggregate([
  {
    $match: {
      userId: 1
    }
  },
  {
    $project: {
      actionsArr: {
        $filter: {
          input: {
            "$objectToArray": "$action"
          },
          cond: {
            $ne: [
              "$$this.k",
              "type"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$actionsArr"
  },
  {
    $group: {
      _id: {
        key: "$actionsArr.k",
        value: "$actionsArr.v"
      },
      sum: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.key",
      values: {
        $push: {
          _id: "$_id.value",
          count: "$sum"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: [
          [
            {
              k: "$_id",
              v: "$values"
            }
          ]
        ]
      }
    }
  }
])

Mongo Playground

user_107703700
Related