deepak deepak - 1 month ago 6
JSON Question

How can transpose mongodb document list to tabular form?

How can transpose mongodb document list to tabular form?

Example:

I have the following list of BsonDocuments

[0] = { "_id" : ObjectId("546ce80d43f5c2b7d8a713bd"), "Address" : "61- A, Pocket- C, Ram Vihar Phase II, UP", "Pincode" : 110091, "Locality" : "Mayur Vihar Phase II", "City" : "Delhi", "Latitude" : 28.618156, "Longitude" : 76.3055877 }

[1] = { "_id" : ObjectId("546ce80d43f5c2b7d8a713bd"), "Address" : "61- A, Pocket- C, Phase II, Rahi", "Pincode" : 110091, "Locality" : "Mayur Vihar Phase II", "City" : "Delhi", "Latitude" : 28.618156, "Longitude" : 77.305877 }

[2] = { "_id" : ObjectId("546ce80d43f5c2b7d8a713bd"), "Address" : "6 B2C - C, Krish Marg II, Delhi", "Pincode" : 110092, "Locality" : "Mayur Vihar Phase II", "City" : "Delhi", "Latitude" : 28.618156, "Longitude" : 76.305877 }


And I want convert this list to tabular like json like

Address:{'61- A, Pocket- C, Ram Vihar Phase II, UP',' '61- A, Pocket- C, Phase II, Rahi ','6 B2C - C, Krish Marg II, Delhi'} ,
Pincode:{'110091','110091','110092'},
...


Similarly for all fields.

So my question is:
is there any library or do I need write code for this?

Answer

You need to write code to aggregate, which may look like:

  • Group by _id equal to null, so that all the records come under the same group.
  • Use the $push operator to form an array of Address, Pincode,.. elements.
  • Project the required fields omitting the _id field.

Code:

db.collection.aggregate([
{$group:{"_id":null,"Address":{$push:"$Address"},"Pincode":{$push:"$Pincode"}}},
{$project:{"_id":0,"Address":1,"Pincode":1}}
])

You can include other fields like Locality,.. as well.

If you want to do it in the client side:

var result = {};
db.collection.find().forEach(function(doc){
var keys = Object.keys(doc);
for(var i=0;i<keys.length;i++)
{
        if(!result[keys[i]]) result[keys[i]] = [];
        result[keys[i]].push(doc[keys[i]]);
}
})
console.log(result);
Comments