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
– Philipp, Commented Jun 12, 2014 at 12:16
Possibly not being clear here. Do you actually want averages or something from the array data? — Neil Lunn
– Neil Lunn, Commented Jun 12, 2014 at 12:19

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

1

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.

answered Jun 12, 2014 at 12:35

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB: convert array elements to fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related