Creating matrix table grouped by columns

Question

I have requirement where I need to list all the projects running inside a specific region. All the project should be added as columns in a table. The number of projects can increase so the table needs to be dynamically created having dynamic columns. Also the table should start with two columns which include information about the category and region which should be repeated for every project. Also we need to group the project by status. So we need column grouping. I don't know whether the below JSON structure is accurate to meet the requirements or do we need to change that

I have JSON similar to this,

[{
  "category": "a",
  "region": "Region 1",
  projects: [{
    "project": "A",
    "resources" : 2,
    "status": green
  }, {
    "project": "B", 
    "resources" : 2,
    "status": green
  }, {
    "project": "C",
    "resources" : 2,
    "status": green
  }]
}, {
  "category": "b",
  "region": "Region 2",
  projects: [{
    "project": "F",
    "resources" : 2,
    "status": red
  }, {
    "project": "A",
    "resources" : 4,
    "status": green
  }]
}]

Is it possible to dynamically create a table grouped by status column. Or should we handle creation of such tables from server side. The table should look similar to this,

<table>
  <tr>
    <th rowspan="2">Category</th>
    <th rowspan="2">Region</th>
    <th colspan="4">Green</th>
    <th colspan="3">Red</th>
  </tr>
  <tr>
    <th>A</th>
    <th>B</th>
    <th>C</th>
    <th>D</th>
    <th>E</th>
    <th>F</th>
    <th>G</th>
  </tr>
  <tr>
    <td>A</td>
    <td>Region 1</td>
    <td>2</td>
    <td>2</td>
    <td>2</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>B</td>
    <td>Region 2</td>
    <td>4</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td>2</td>
    <td></td>
  </tr>
  </table>

I want whether this can be achieved dynamically via jQuery using the above JSON structure or do we need to modify the JSON structure.

Answer 1

You can do this in JavaScript using a function such as

"use strict";

function groupBy(array, selectKey) {
  return array.reduce((groups, element) => {
      const key = selectKey(element);
      if (groups[key]) {
        groups[key].push(element);
      } 
      else {
        groups[key] = [element];
      }

      return groups;
    }, {});
}

This will project an array into an object with properties for each distinct key projected from the objects in the array. The value of each property will be an array of the values that produced that key under whatever selectKey function we passed.

Note: selectKey should return a string or a number and, irregardless, its result will be coerced into a string.

Now, given your array, we can group it.

Lets start by grouping by category:

"use strict";

function groupBy(array, selectKey) {
  return array.reduce((groups, element) => {
      const key = selectKey(element);
      if (groups[key]) {
        groups[key].push(element);
      } 
      else {
        groups[key] = [element];
      }
      
      return groups;
    }, {});
}

const xs = [{
  "category": "a",
  "region": "Region 1",
  projects: [{
    "project": "A",
    "resources": 2,
    "status": 'green'
  }, {
    "project": "B",
    "resources": 2,
    "status": 'green'
  }, {
    "project": "C",
    "resources": 2,
    "status": 'green'
  }]
}, {
  "category": "b",
  "region": "Region 2",
  projects: [{
    "project": "F",
    "resources": 2,
    "status": 'red'
  }, {
    "project": "A",
    "resources": 4,
    "status": 'green'
  }]
}];

const byCategory = groupBy(xs, x => x.category);

console.log(byCategory);

Now, what if we need to group by multiple columns? We could augment groupBy with logic to take a custom comparison function, but we can cheat by using string concatenation.

In the following example, we group by category and by region:

"use strict";

function groupBy(array, selectKey) {
  return array.reduce((groups, element) => {
      const key = selectKey(element);
      if (groups[key]) {
        groups[key].push(element);
      } 
      else {
        groups[key] = [element];
      }
      
      return groups;
    }, {});
}

const xs = [{
  "category": "a",
  "region": "Region 1",
  projects: [{
    "project": "A",
    "resources": 2,
    "status": 'green'
  }, {
    "project": "B",
    "resources": 2,
    "status": 'green'
  }, {
    "project": "C",
    "resources": 2,
    "status": 'green'
  }]
}, {
  "category": "b",
  "region": "Region 2",
  projects: [{
    "project": "F",
    "resources": 2,
    "status": 'red'
  }, {
    "project": "A",
    "resources": 4,
    "status": 'green'
  }]
}];

const byCategoryAndRegion = groupBy(
    xs,
    x => `category: ${x.category}, region: ${x.region}`
  );

console.log(byCategoryAndRegion);

I'm not entirely clear on the structure and orientation of your data, but this should be enough to get you going.