MongoDB: convert array elements to fields

Question

I am on MongoDB 2.6.0, and I have a collection with documents like this:

{ "mid" : 1021, 
  "day" : 298,
  "data":[
  {"ts" : 1,"kwh" : 0.017},
  {"ts" : 2,"kwh" : 0.018},
  {"ts" : 3,"kwh" : 0.019},
  ... ] }

I would like to flatten the array elements into individual fields like this:

{ "mid" : 1021, 
  "day" : 298,
  "ts1" : 0.017,
  "ts2" : 0.018,
  "ts3" : 0.019,
  ...
}

This looks like it should be possible with the Aggregation framework, but I really can't figure out how to re-project the data array's "kwh" elements based on the value of "ts".

Anybody know how to do this?

Thanks for any help!

Are you sure you want to do this? Most queries will be a lot easier to do when you keep the data-array structure. — Philipp
Possibly not being clear here. Do you actually want averages or something from the array data? — Neil Lunn

Neil Lunn Neil Lunn · Accepted Answer · 2014-06-12T12:35:23

More of a mapReduce task really:

db.collection.mapReduce(
    function () {

      var copy = this;
      var tdata = copy.data;
      delete copy.data;

      tdata.forEach(function(data) {
        var key = "ts" + data.ts;
        copy[key] = data.kwh;
      });

      var id = copy._id;
      delete copy["_id"];

      emit( id, copy );

    },
    function(){},
    { "out": { "inline": 1 } }
)

At present you cannot flexibly specify the value of a "key" using the aggregation framework. You wold have to explicitly name each key through projection in order to get the output you want.

MapReduce does not give "exactly" the same output as you want do to how it works. But it is the closest you will get without explicitly naming the keys.

MongoDB: convert array elements to fields

1 Answers