Mongo DB collecting properties as a an array

user_11028462 0
Viewed 918

I am a novice in Mongo database: I have a document in a collection, let's say with 5 properties(A,B,C,D,E). And, I have documents in the collection that contains all or some of the above properties. I want to write a query that will append the property values from all of the documents.

Document 1 = {Id:123, data:{A:3, B:4, C:34, D:45, E:4}}
Document 2 = {Id:123, data:{A:5, E:33}}
Document 3 = {Id:123, data:{B:499, C:24,  E:39}}
Document 4 = {Id:123, data:{A:63, B:34, C:534, D:845, E:84}}
Document 5 = {Id:123, data:{E:4}}

For the ID123, i want the query output to be Output document 

{Id:123, A:[3,5,63], B:[4,499,34],C:[34,24,534],D:[45,845],E:[4,33,39,84,4]}
aggregation-framework mongodb
2 Answers

You can try this.

db.getCollection('Test02').aggregate([
    {$project: {Id: 1, data: { $objectToArray: "$data" } } },
    {$unwind: {path: "$data" } },
    {$group: { _id: { "Id": "$Id", "key": "$data.k"}, data: { $push:  "$data.v"  } } },
    {$group: { _id: "$_id.Id" , data: { $push:  {k:"$_id.key", v:"$data" } } } },
    {$project: {  data: { $arrayToObject: "$data" } } },
    {$replaceRoot: { newRoot: { $mergeObjects: [  {Id: "$_id"},  "$data" ] } } }
])

Result:

{
    "Id" : 123.0,
    "E" : [ 
        4.0, 
        33.0, 
        39.0, 
        84.0, 
        4.0
    ],
    "D" : [ 
        45.0, 
        845.0
    ],
    "C" : [ 
        34.0, 
        24.0, 
        534.0
    ],
    "A" : [ 
        3.0, 
        5.0, 
        63.0
    ],
    "B" : [ 
        4.0, 
        499.0, 
        34.0
    ]
}
user_85001100

Simply group by Id, pushing each data field in array.

db.collection.aggregate([
  {
    $group: {
      _id: "$Id",
      A: {
        $push: "$data.A"
      },
      B: {
        $push: "$data.B"
      },
      C: {
        $push: "$data.C"
      },
      D: {
        $push: "$data.D"
      },
      E: {
        $push: "$data.E"
      }
    }
  }
])

will output exactly what you need

Mongo playground

user_22768980
Related