bobbinson bobbinson - 28 days ago 8
JSON Question

How do I query mongo to show the results in the format I want?

completely new to mongodb and I am trying to get a query working for some reports in the format I need.

Here is the results from my Assets Collection:

db.getCollection('Asset').find({})


Result:

/* 1 */
{
"_id" : ObjectId("58176238f23ee4040a48846c"),
"AssetTypeId" : "58176225f23ee4040a48846b",
"AssetTypeName" : "Chairs",
"Fields" : [
{
"_id" : "5770ae6cf500fd7675c234c5",
"Title" : "Asset Name",
"Value" : "Fancy Chair"
},
{
"_id" : "576b7593f500fd7675c234b9",
"Title" : "Status",
"Value" : "Missing"
},
{
"_id" : "57b2b52b31f7c7d80db34a23",
"Title" : "Location",
"Value" : "Office")
}
]
}

/* 2 */
{
"_id" : ObjectId("5817638ff23ee4040a48846e"),
"AssetTypeId" : "58176225f23ee4040a48846b",
"AssetTypeName" : "Chairs",
"Fields" : [
{
"_id" : "5770ae6cf500fd7675c234c5",
"Title" : "Asset Name",
"Value" : "Basic Chair"
},
{
"_id" : "576b7593f500fd7675c234b9",
"Title" : "Status",
"Value" : "Stores"
},
{
"_id" : "57b2b52b31f7c7d80db34a23",
"Title" : "Location",
"Value" : "Home")
}
]
}

/* 3 */
{
"_id" : ObjectId("581767f3f23ee4040a488471"),
"AssetTypeId" : "5817678ff23ee4040a488470",
"AssetTypeName" : "Table",
"Fields" : [
{
"_id" : "5770ae6cf500fd7675c234c5",
"Title" : "Asset Name",
"Value" : "Fancy Table"
},
{
"_id" : "576b7593f500fd7675c234b9",
"Title" : "Status",
"Value" : "Active"
},
{
"_id" : "57b2b52b31f7c7d80db34a23",
"Title" : "Location",
"Value" : "Office")
}
]
}


This is the end result I'm trying to get from the query:

[
{"Asset Name":"Fancy Chair", "Status":"Missing","Location":"Office","AssetTypeName":"Chairs"},
{"Asset Name":"Basic Chair", "Status":"Stores","Location":"Office","AssetTypeName":"Chairs"},
{"Asset Name":"Fancy Table", "Status":"Active","Location":"Office","AssetTypeName":"Table"}
]

Answer

You just need to transform the mongodb Asset results into your preferred format by iterating your results and creating a new array of objects:

function processAssets(assets){
   var results = [];
   for (var i=0;i<assets.length;i++){
      var asset = assets[i];
      var result = {AssetTypeName: asset.AssetTypeName};
      //AssetTypeName is not in the Fields array
      for (var f=0;f<asset.Fields.length;f++){
         var field = asset.Fields[f];
         result[field.Title] = field.Value;
         //this will make result['Asset Name'] = 'Fancy Chair', etc
      }
      results.push(result);
   } 
   return results;
}

A more mongodb way is to use the cursor.map method:

db.Asset.find().map(function(asset){
    var result = {AssetTypeName: asset.AssetTypeName};
    for (var f=0;f<asset.Fields.length;f++){
       var field = asset.Fields[f];
       result[field.Title] = field.Value;
    }
    return result;
});
Comments