Pivotting SQL Table Response to JSON in JavaScript

Question

Issue

Our API returns two formats JSON and this SQL table format. I'm not sure what it is truly called so I'll give a few examples below. Like all API responses, it can be a list or a single response but it is always in the same format. I'm creating a utility function for this SQL table format to be converted into a JSON object so the front-end only needs to be concerned with a single format, JSON.

Problem

Convert SQL Table Response to JSON

Examples of the SQL table format

Here is the SQL table format as a success message.

{
 "COLUMNS":["SUCCESS","RETMESSAGE"],
 "DATA":[[true,"Order Approved"]]
}

Here is an example of a list

{
 "COLUMNS":["PRODUCTID","PRODUCTNAME"],
 "DATA":[[1001,"Product1"],[1002,"Product2"],[1003,"Product3"]]
}

Current Solution

The current solution is the following. It works well but I keep coming back to it and thinking there is a more elegant way of writing this.

const request = {
 "COLUMNS":["SUCCESS","RETMESSAGE"],
 "DATA":[[true,"Order Approved"]]
 };
const desiredFormat = [{
 "SUCCESS":true,
 "RETMESSAGE":"Order Approved"
 }];
function tableToJSON (dataset) {
 const data = dataset.DATA;
 const columns = dataset.COLUMNS;
 const jsonData = [];
 data.forEach( (row) => {
 const json = {};
 row.forEach( (item, index) => {
 json[columns[index]] = item;
 });
 jsonData.push(json);
 });
 return jsonData;
 }
const formattedResponse = tableToJSON(request);
console.log(JSON.stringify(formattedResponse) === JSON.stringify(desiredFormat))
// Outputs: True

• Michael Warner: Is that better?
• dfhwze: Ah you want to pivot data. Now it's clear to me.

Answer 1

Whenever you have code that sets up an array, then has a loop to push items into that array, Array.map() could be used to condense that code. For example, these lines:

const jsonData = [];
data.forEach( (row) => {
 const json = {};
 row.forEach( (item, index) => {
 json[columns[index]] = item;
 });
 jsonData.push(json);
});
return jsonData;

Could be simplified to this:

return data.map( (row) => {
 const json = {};
 row.forEach( (item, index) => {
 json[columns[index]] = item;
 });
 return json;
});

You could optionally simplify the inner loop using Array.reduce()

return data.map( (row) => {
 return row.reduce( (json, item, index) => {
 json[columns[index]] = item;
 return json;
 }, {});
});

There are some good exercises in Functional Programming in Javascript where you practice implementing some of those methods.