user1735894 user1735894 - 2 months ago 9
JSON Question

Combine JSON "rows" into new object with "colums"

I have a JSON object array that looks something like this

[
{"PM":"Jane","e":"j@nunya.com","h":"15.00","w":"10/30/2016 12:00:00 AM","c":"John","p":"Happy Town USA"},
{"PM":"Jane","e":"j@nunya.com","h":"11.00","w":"11/06/2016 12:00:00 AM","c":"John","p":"Happy Town USA"},
{"PM":"Jill","e":"j@nunya.com","h":"21.00","w":"10/30/2016 12:00:00 AM","c":"John","p":"Sad Town USA"},
{"PM":"Jill","e":"j@nunya.com","h":"12.00","w":"11/06/2016 12:00:00 AM","c":"John","p":"Sad Town USA"}
]


Looking at the data all "columns" for a given PM,e, p, c combination the data is same except the w and the h fields. I need to transform this data into a new object that would look something like this.

[
{PM:"Jane",e:"j@nunya.com",c:"John",p:"Happy Town USA",
Details:[{"w":"10/30/2016","h":"15.00"},{"w":"11/06/2016","h":"11.00"}]},
{PM:"Jill",e:"j@nunya.com",c:"John",p:"Sad Town USA",
Details:[{"w":"10/30/2016","h":"21.00"},{"w":"11/06/2016","h":"12.00"}]}
]


It's kind of like making columns from rows in a SQL query, unfortunately I have little control over the source data so that is not an option.

Answer

var source = [{
  "PM": "Jane",
  "e": "j@nunya.com",
  "h": "15.00",
  "w": "10/30/2016 12:00:00 AM",
  "c": "John",
  "p": "Happy Town USA"
}, {
  "PM": "Jane",
  "e": "j@nunya.com",
  "h": "11.00",
  "w": "11/06/2016 12:00:00 AM",
  "c": "John",
  "p": "Happy Town USA"
}, {
  "PM": "Jill",
  "e": "j@nunya.com",
  "h": "21.00",
  "w": "10/30/2016 12:00:00 AM",
  "c": "John",
  "p": "Sad Town USA"
}, {
  "PM": "Jill",
  "e": "j@nunya.com",
  "h": "12.00",
  "w": "11/06/2016 12:00:00 AM",
  "c": "John",
  "p": "Sad Town USA"
}];

// First we'll transform the source into a dictionary
var dict = source.reduce(function(p, c) {
  // We'll use the combination of PM,e,c and p to build a key
  var key = [c.PM, c.e, c.c, c.p].join("-");
  if (p[key]) {
    // If we've seen this key before, we'll just add to the Details
    p[key].Details.push({
      w: c.w,
      h: c.h
    });
  } else {
    // otherwise we create a new entry and populate it
    p[key] = {
      PM: c.PM,
      c: c.c,
      e: c.e,
      p: c.p,
      Details: [{
        w: c.w,
        h: c.h
      }]
    }
  }
  return p;
}, {});

// We now have a dictionary
console.log(dict);

// If you really need an array (and don't care about order), we can transform
// the dictonary to an array (order is not guaranteed here)
var result = Object.keys(dict).map(function(a) {
  return dict[a];
});

console.log(result);