CODEWITHSUNDEEP
javascriptjson
user1735894
user1735894

Reputation: 323

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

I have a JSON object array that looks something like this

 [
    {"PM":"Jane","e":"[email protected]","h":"15.00","w":"10/30/2016 12:00:00 AM","c":"John","p":"Happy Town USA"},
    {"PM":"Jane","e":"[email protected]","h":"11.00","w":"11/06/2016 12:00:00 AM","c":"John","p":"Happy Town USA"},
    {"PM":"Jill","e":"[email protected]","h":"21.00","w":"10/30/2016 12:00:00 AM","c":"John","p":"Sad Town USA"},
    {"PM":"Jill","e":"[email protected]","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:"[email protected]",c:"John",p:"Happy Town USA",
     Details:[{"w":"10/30/2016","h":"15.00"},{"w":"11/06/2016","h":"11.00"}]},
    {PM:"Jill",e:"[email protected]",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.

Upvotes: 0

Views: 103

Answers (3)

David He
David He

Reputation: 54

Java approach only: This question sounds very challenging for a quick solution. I got a partial solution here using Java Oson library I created, here is the source file:

@Test
public void testTableMerge2() {
    List<Map> list = oson.readValue("rows2Columns.txt");

    Map<String, List> collected = new HashMap<>();

    for (Map map: list) {
        String key = (String) map.get("PM");
        List obj = collected.get(key);

        if (obj == null) {
            obj = new ArrayList();

            collected.put(key, obj);
        }

        obj.add(map);
    }

    Config config = merge.getConfig();
    config.numericValue = NUMERIC_VALUE.MERGE_UNIQUE;
    config.nonnumericalValue = NONNUMERICAL_VALUE.MERGE_UNIQUE;
    config.listValue = LIST_VALUE.MERGE_UNIQUE;

    Map<String, Object> results = new HashMap<>();

    for (String key: collected.keySet()) {
        results.put(key, merge.merge(collected.get(key)));
    }

    Collection result = results.values();

    // oson.pretty();
    String json = oson.serialize(result);

    String expected = "[{\"PM\":\"Jill\",\"e\":\"[email protected]\",\"h\":[\"21.00\",\"12.00\"],\"w\":[\"10/30/2016 12:00:00 AM\",\"11/06/2016 12:00:00 AM\"],\"c\":\"John\",\"p\":\"Sad Town USA\"},{\"PM\":\"Jane\",\"e\":\"[email protected]\",\"h\":[\"15.00\",\"11.00\"],\"w\":[\"10/30/2016 12:00:00 AM\",\"11/06/2016 12:00:00 AM\"],\"c\":\"John\",\"p\":\"Happy Town USA\"}]";

    assertEquals(expected, json);
}

Upvotes: 0

Matt Burland
Matt Burland

Reputation: 45155

var source = [{
  "PM": "Jane",
  "e": "[email protected]",
  "h": "15.00",
  "w": "10/30/2016 12:00:00 AM",
  "c": "John",
  "p": "Happy Town USA"
}, {
  "PM": "Jane",
  "e": "[email protected]",
  "h": "11.00",
  "w": "11/06/2016 12:00:00 AM",
  "c": "John",
  "p": "Happy Town USA"
}, {
  "PM": "Jill",
  "e": "[email protected]",
  "h": "21.00",
  "w": "10/30/2016 12:00:00 AM",
  "c": "John",
  "p": "Sad Town USA"
}, {
  "PM": "Jill",
  "e": "[email protected]",
  "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);

Upvotes: 1

Jared Smith
Jared Smith

Reputation: 21965

You want to use reduce to accomplish these kinds of transformations in JavaScript:

var arr = /* your array of objects goes here */;

var results = Object.values(arr.reduce(function(obj, row) {
  var pm = row.PM;

  // here we check to see if we already have an aggregator for that pm
  if (!obj[pm]) {
    obj[pm] = {
      PM: pm,
      e: row.e,
      c: row.c,
      p: row.p
      Details: []
    };
  }

  // now we need to add a record to the Details
  obj[pm].Details.push({ w: row.w, h: row.h });

  // common mistake using reduce is to forget 
  // to return the accumulator
  return obj;
}, {}));

You can convert back to a JSON string with JSON.stringify. Note that Object.values is not yet implemented in every JavaScript environment, you may need to polyfill it.

Upvotes: 2

Related Questions