How parse JSON with complex nesting and unnamed array?

Question

I am trying to figure out how to parse the JSON response I receive when I make a call to a specific database (JSON response shown below) using vanilla javascript - and so far I have not had any luck. I am placing an API call to the Quickbase database and they have a standard formatting for their JSON response. The API i am calling can be found at this link: https://developer.quickbase.com/operation/runQuery.

Here is what a response from the API call looks like

{
    "data": [
        {
            "6": {
                "value": 11.0
            },
            "69": {
                "value": "A"
            },
            "70": {
                "value": "B"
            }
        },
        {
            "6": {
                "value": 11.0
            },
            "69": {
                "value": "C"
            },
            "70": {
                "value": "D"
            }
        }
    ],
    "fields": [
        {
            "id": 6,
            "label": "Related Invoice",
            "type": "numeric"
        },
        {
            "id": 69,
            "label": "TEST1",
            "type": "text"
        },
        {
            "id": 70,
            "label": "TEST2",
            "type": "text"
        }
    ],
    "metadata": {
        "numFields": 3,
        "numRecords": 2,
        "skip": 0,
        "totalRecords": 2
    }
}

And this is what I want to parse it into (do NOT need to omit the JSON not shown here - I just did that for clarity)

{
    "data": [
        {
            "Related Invoice":11.0,
            "TEST1":"A",
            "TEST2":"B"
        },
        {
            "Related Invoice":11.0,
            "TEST1":"C",
            "TEST2":"D"
        }
    ]
}

Below is the full javascript code i am using

let headers = {
    'QB-Realm-Hostname': 'XXXXXX',
        'User-Agent': 'Invoice',
    'Authorization': 'XXXXXX',
    'Content-Type': 'application/json'
}

let body = 

{
  "from": "bq2paydp2",
  "select": [
    6,
    69,
    70
  ],
  "where": "{6.EX.11}",
  "sortBy": [
    {
      "fieldId": 6,
      "order": "ASC"
    },
    {
      "fieldId": 69,
      "order": "ASC"
    }
  ]
}


const xmlHttp = new XMLHttpRequest();
xmlHttp.open('POST', 'https://api.quickbase.com/v1/records/query', true);
for (const key in headers) {
  xmlHttp.setRequestHeader(key, headers[key]);
}
xmlHttp.onreadystatechange = function() {
  if (xmlHttp.readyState === XMLHttpRequest.DONE) {
    console.log(xmlHttp.responseText);


let line_items = JSON.parse(this.responseText, dataReviver);

console.log(line_items);

//function dataReviver (key, value) {
//if (key = 6)
//  {
//      var newHeaderName = 99;
//      return newHeaderName;
//  }
//
//  return value;
//} 


//document.getElementById('abc').innerHTML =  line_items.data[0][6].value;
   
function generateTableHead(table,tableData) {
  let thead = table.createTHead();
  let row = thead.insertRow();
  for (let key of tableData) {
    let th = document.createElement("th");
    let text = document.createTextNode(key);
    th.appendChild(text);
    row.appendChild(th);
  }
};

function generateTable(table, tableData) {
  for (let element of tableData) {
    let row = table.insertRow();
    for (key in element) {
      let cell = row.insertCell();
      let text = document.createTextNode(element[key]);
      cell.appendChild(text);
    }
  }
};


let table = document.querySelector("table");
let tableData = Object.keys(line_items.data[0]);
generateTableHead(table, tableData);
generateTable(table, line_items.data);

  }
};


xmlHttp.send(JSON.stringify(body));

This is what I am trying to achieve

|-----------------------------------------|
| Count | Related Invoice | TEST1 | TEST2 |
|-------|-----------------|-------|-------|
|   1   |       11.0      |   A   |   B   |
|-------|-----------------|-------|-------|      
|   2   |       11.0      |   C   |   D   |
|-----------------------------------------|

I need to accomplish 3 things:

• #1 Rename "6", "69 and "70" to the corresponding fields.label ( "Related Invoice", "TEST1" and "TEST2" ).
• #2 Take the value of the objects nested under the objects shown above in #1 ( 11.0, "A", "B", ... ) and set them as the value of the objects shown in #1 above. This would, for example, make 6 (Related Invoice) the key and 11.0 the value.
• #3 I ultimately want to display this in a table on a webpage. the html and css I can handle its the Javascript and JSON that I am not that great with.

If you need me to clarify anymore information please let me know.

Answer 1

To transform the data in the way you're looking, you'll need to loop over the data key in the object and create a new array based on the result of the loop.

A way to do this is with Array.prototype.map(). With this you can loop over each item in the array and return a new value.

In this map loop you are looping over each item in the data array. For each item you'll want to get the id and label from the fields array and use that array to create a new object. To create a new object within in a loop, you could use the Array.prototype.reduce() method.

So in this case you'll have a nested loop. The inner loop will loop over the fields array and uses the id to get the correct value from data array. It then returns an object with the label and the value set like you requested. The surrounding map method will then return a new array with objects. Tadaa, magic!

const response = {
  "data": [{
      "6": {
        "value": 11.0
      },
      "69": {
        "value": "A"
      },
      "70": {
        "value": "B"
      }
    },
    {
      "6": {
        "value": 11.0
      },
      "69": {
        "value": "C"
      },
      "70": {
        "value": "D"
      }
    }
  ],
  "fields": [{
      "id": 6,
      "label": "Related Invoice",
      "type": "numeric"
    },
    {
      "id": 69,
      "label": "TEST1",
      "type": "text"
    },
    {
      "id": 70,
      "label": "TEST2",
      "type": "text"
    }
  ],
  "metadata": {
    "numFields": 3,
    "numRecords": 2,
    "skip": 0,
    "totalRecords": 2
  }
};

const transformResponseData = (response) => {
  const { data, fields } = response;

  // Return a new array with objects based on the values
  // of the data and fields arrays.
  const revivedData = data.map(entry =>
    fields.reduce((object, { id, label }) => {
      object[label] = entry[id].value;
      return object;
    }, {})
  );

  // Combine the original object with the new data key.
  return {
    ...response,
    data: revivedData
  };
};

const createTable = ({ data, fields }) => {
  const table = document.createElement('table');
  const tHead = table.createTHead();
  const tBody = table.createTBody();

  
  const tHeadRow = tHead.insertRow();
  
  // Create the counts cell manually.
  const tHeadRowCountCell = document.createElement('th');
  tHeadRowCountCell.textContent = 'Count';
  tHeadRow.append(tHeadRowCountCell);
    
  // Create a head for each label in the fields array.
  for (const { label } of fields) {
    const tHeadRowCell = document.createElement('th');
    tHeadRowCell.textContent = label;
    tHeadRow.append(tHeadRowCell);
  }

  // Output all the values of the new data array.
  for (const [index, entry] of data.entries()) {
    const tBodyRow = tBody.insertRow();
    
    // Create a new array with the index and the
    // values from the object.
    const values = [
      index + 1,
      ...Object.values(entry)
    ];

    // Loop over the combined values array.
    for (const [index, value] of values.entries()) {
      const tBodyCell = tBodyRow.insertCell();
      tBodyCell.textContent = index === 1 ?
        value.toFixed(1) :
        value;
    }
  }

  return table;
};

const data = transformResponseData(response);
const table = createTable(data);
document.body.append(table);