I want to restructure a json object array to show data in tabular format. I have managed to create an object with name as key and values as an array of each type using below lodash command

const grouped = _.groupBy(dayTargetDetails, trgt => trgt.NAME);

And I want the data in below format

Sample JSON Data:

const dayTargetDetails = [
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-01",
        "ORDERS": 45
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-02",
        "ORDERS": 75
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-03",
        "ORDERS": 15
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-04",
        "ORDERS": 64
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-05",
        "ORDERS": 19
    },
    {
        "NAME": "ORANGE",
        "ORDER_DT": "2022-10-01",
        "ORDERS": 18
    },
    {
        "NAME": "ORANGE",
        "ORDER_DT": "2022-10-02",
        "ORDERS": 26
    }
]

CodePudding user response：

Using reduce() to compile the JSON records into corresponding NAME categories:

function compileDayTD(dayTargetDetails) {
    return dayTargetDetails.reduce(function (prev, itm) {

        // check for existing NAME key, instantiate with array if none
        (undefined !== prev[itm.NAME] || (prev[itm.NAME] = [])) 
    
        // append item to NAME array
        && prev[itm.NAME].push(itm);

        return prev;
    }, {});
}

const dayTargetDetails = [
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-01",
        "ORDERS": 45
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-02",
        "ORDERS": 75
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-03",
        "ORDERS": 15
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-04",
        "ORDERS": 64
    },
    {
        "NAME": "APPLE",
        "ORDER_DT": "2022-10-05",
        "ORDERS": 19
    },
    {
        "NAME": "ORANGE",
        "ORDER_DT": "2022-10-01",
        "ORDERS": 18
    },
    {
        "NAME": "ORANGE",
        "ORDER_DT": "2022-10-02",
        "ORDERS": 26
    }
]

console.log(compileDayTD(dayTargetDetails));

Produces the JSON object:

{
  "APPLE": [
    {
      "NAME": "APPLE",
      "ORDER_DT": "2022-10-01",
      "ORDERS": 45
    },
    {
      "NAME": "APPLE",
      "ORDER_DT": "2022-10-02",
      "ORDERS": 75
    },
    {
      "NAME": "APPLE",
      "ORDER_DT": "2022-10-03",
      "ORDERS": 15
    },
    {
      "NAME": "APPLE",
      "ORDER_DT": "2022-10-04",
      "ORDERS": 64
    },
    {
      "NAME": "APPLE",
      "ORDER_DT": "2022-10-05",
      "ORDERS": 19
    }
  ],
  "ORANGE": [
    {
      "NAME": "ORANGE",
      "ORDER_DT": "2022-10-01",
      "ORDERS": 18
    },
    {
      "NAME": "ORANGE",
      "ORDER_DT": "2022-10-02",
      "ORDERS": 26
    }
  ]
}

CodePudding user response：

The following code defines two functions tabulate and tabulatedToString.

Function tabulate takes the input data, the names of the horizontal and vertical fields to be used in the tabulation, and the name of the field to be used for the table cell value.

The input data is enumerated; the horizontal and vertical headings are populated; and row objects are created and populated, keyed on the vertical field (here, the date). cellZero is the value to be used in the top left corner of the table.

Function tabulatedToString provides an example transformation of the tabulated data to a formatted string; this would need tweaking to output an HTML table.

const data = [
  { "NAME": "APPLE", "ORDER_DT": "2022-10-01", "ORDERS": 45 }, 
  { "NAME": "APPLE", "ORDER_DT": "2022-10-02", "ORDERS": 75 }, 
  { "NAME": "APPLE", "ORDER_DT": "2022-10-03", "ORDERS": 15 }, 
  { "NAME": "APPLE", "ORDER_DT": "2022-10-04", "ORDERS": 64 }, 
  { "NAME": "APPLE", "ORDER_DT": "2022-10-05", "ORDERS": 19 }, 
  { "NAME": "ORANGE", "ORDER_DT": "2022-10-01", "ORDERS": 18 }, 
  { "NAME": "ORANGE", "ORDER_DT": "2022-10-02", "ORDERS": 26 }]

const tabulate = (data, { x, y, cellZero = y, v }) => {
    const xHeadings = new Set
    const yHeadings = new Set
    const rows = new Map
    
    for(let d of data) {
        rows.get(d[y]) || rows.set(d[y], {})        
        xHeadings.add(d[x])
        yHeadings.add(d[y])
        rows.get(d[y])[d[x]] = d[v]
    }
    
    return { cellZero, xHeadings, yHeadings, rows }
}

const tablulatedToString = ({ cellZero, xHeadings, yHeadings, rows }) => {
    const acc = [`${cellZero} | ${[...xHeadings].join(' |')}`]
    
    for(let yh of yHeadings) {
       const row = [`${yh}`]
       
       for(let xh of xHeadings) {
          row.push(rows.get(yh)[xh])
       }
       
       acc.push(row.join(' | '))
    }
    
    return acc.join('\n')
}

const options = { x: 'ORDER_DT', y: 'NAME', cellZero: 'Fruit', v: 'ORDERS' }
const tabulated = tabulate(data, options)
console.log(tablulatedToString(tabulated))