How to parse paginated JSON API response with complex nesting and unnamed array?

Question

I have built (with the help of @EmielZuurbier) an invoice template which places an API call to Quickbase. The API response is paginated. How can I parse the paginated response into a single table?

• Quickbase API Endpoint: https://developer.quickbase.com/operation/runQuery
• Quickbase paginated metadata explanation: https://developer.quickbase.com/pagination

Here is what a response from the API call looks like (I deleted most of the items under data or else it would have been to long to post on stackoverflow

{
    "data": [
        {
            "15": {
                "value": "F079427"
            },
            "19": {
                "value": 50.0
            },
            "48": {
                "value": "(S1)"
            },
            "50": {
                "value": "2021-03-01"
            },
            "8": {
                "value": "71 Wauregan Rd, Danielson, Connecticut 06239"
            }
        },
        {
            "15": {
                "value": "F079430"
            },
            "19": {
                "value": 50.0
            },
            "48": {
                "value": "(S1)"
            },
            "50": {
                "value": "2021-03-01"
            },
            "8": {
                "value": "7 County Home Road, Thompson, Connecticut 06277"
            }
        },
        {
            "15": {
                "value": "F079433"
            },
            "19": {
                "value": 50.0
            },
            "48": {
                "value": "(S1)"
            },
            "50": {
                "value": "2021-03-16"
            },
            "8": {
                "value": "12 Bentwood Street, Foxboro, Massachusetts 02035"
            }
        }
    ],
    "fields": [
        {
            "id": 15,
            "label": "Project Number",
            "type": "text"
        },
        {
            "id": 8,
            "label": "Property Adress",
            "type": "address"
        },
        {
            "id": 50,
            "label": "Date Completed",
            "type": "text"
        },
        {
            "id": 48,
            "label": "Billing Codes",
            "type": "text"
        },
        {
            "id": 19,
            "label": "Total Job Price",
            "type": "currency"
        }
    ],
    "metadata": {
        "numFields": 5,
        "numRecords": 500,
        "skip": 0,
        "totalRecords": 766
    }
}

Below is the full javascript code i am using

const urlParams = new URLSearchParams(window.location.search);
//const dbid = urlParams.get('dbid');//
//const fids = urlParams.get('fids');//
let rid = urlParams.get('rid');
//const sortLineItems1 = urlParams.get('sortLineItems1');//
//const sortLineItems2 = urlParams.get('sortLineItems2');//
let subtotalAmount = urlParams.get('subtotalAmount');
let discountAmount = urlParams.get('discountAmount');
let creditAmount = urlParams.get('creditAmount');
let paidAmount = urlParams.get('paidAmount');
let balanceAmount = urlParams.get('balanceAmount');
let clientName = urlParams.get('clientName');
let clientStreetAddress = urlParams.get('clientStreetAddress');
let clientCityStatePostal = urlParams.get('clientCityStatePostal');
let clientPhone = urlParams.get('clientPhone');
let invoiceNumber = urlParams.get('invoiceNumber');
let invoiceTerms = urlParams.get('invoiceTerms');
let invoiceDate = urlParams.get('invoiceDate');
let invoiceDueDate = urlParams.get('invoiceDueDate');
let invoiceNotes = urlParams.get('invoiceNotes');


const formatCurrencyUS = function (x) {
    return new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(x);
}


let subtotalAmountFormatted = formatCurrencyUS(subtotalAmount);
let discountAmountFormatted = formatCurrencyUS(discountAmount);
let creditAmountFormatted = formatCurrencyUS(creditAmount);
let paidAmountFormatted = formatCurrencyUS(paidAmount);
let balanceAmountFormatted = formatCurrencyUS(balanceAmount);


document.getElementById("subtotalAmount").innerHTML = `${subtotalAmountFormatted}`;
document.getElementById("discountAmount").innerHTML = `${discountAmountFormatted}`;
document.getElementById("creditAmount").innerHTML = `${creditAmountFormatted}`;
document.getElementById("paidAmount").innerHTML = `${paidAmountFormatted}`;
document.getElementById("balanceAmount").innerHTML = `${balanceAmountFormatted}`;
document.getElementById("clientName").innerHTML = `${clientName}`;
document.getElementById("clientStreetAddress").innerHTML = `${clientStreetAddress}`;
document.getElementById("clientCityStatePostal").innerHTML = `${clientCityStatePostal}`;
document.getElementById("clientPhone").innerHTML = `${clientPhone}`;
document.getElementById("invoiceNumber").innerHTML = `${invoiceNumber}`;
document.getElementById("invoiceTerms").innerHTML = `${invoiceTerms}`;
document.getElementById("invoiceDate").innerHTML = `${invoiceDate}`;
document.getElementById("invoiceDueDate").innerHTML = `${invoiceDueDate}`;
document.getElementById("invoiceNotes").innerHTML = `${invoiceNotes}`;


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


let body =

{
    "from": "bq9dajvu5",
    "select": [
        15,
        8,
        50,
        48,
        19
    ],
    "where": `{25.EX.${rid}}`,
    "sortBy": [
        {
            "fieldId": 50,
            "order": "ASC"
        },
        {
            "fieldId": 8,
            "order": "ASC"
        }
    ],
    "options": {
        "skip": 0
    }
}


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);
        console.log(line_items);





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

            //***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 {
                ...line_items,
                data: revivedData
            };
        };





        const createTable = ({ data, fields }) => {
            const table = document.getElementById('line_items');                //const table = document.createElement('table'); 
            const tHead = document.getElementById('line_items_thead');      //const tHead = table.createTHead();
            const tBody = document.getElementById('line_items_tbody');      //const tBody = table.createTBody();
            //***Create a head for each label in the fields array***//
            const tHeadRow = tHead.insertRow();



            // ***Create the counts cell manually***//
            const tHeadRowCountCell = document.createElement('th');
            tHeadRowCountCell.textContent = 'Count';
            tHeadRow.append(tHeadRowCountCell);



            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 === 5 ?
                        Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(value) ://value.toFixed(2) :
                        value;


                }
            }
            return table;
        };





        const data = transformResponseData(line_items);
        const table = createTable(data);
        document.getElementById("line_items_div").append(table)                 //.innerHTML = table <-- this does not work//       //document.body.append(table); 
        console.log(data);





    }
};

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

This is what I am trying to achieve (address is only shown as xxx so the table fits nicely on stackoverflow)

Count	Project Number	Property Address	Date Completed	Billing Codes	Total Job Price
1	F079427	xxx	2021-03-01	(S1)	$50.00
2	F079430	xxx	2021-03-01	(S1)	$50.00
3	F079433	xxx	2021-03-16	(S1)	$50.00

My thoughts on how to accomplish this

For the request formula we probably need a function that loops and it will skip an amount of records that is === to the sum of all the numRecords for every request made until skip + numRecords === totalRecords

so for example if the totalRecords = 1700

1. First request {"skip": 0} returns numRecords=500
2. Second request {"skip": 500} returns numRecords=500
3. Third request {"skip": 1000} returns numRecords=500
4. Fourth request {"skip": 1500} returns numRecords=200

On the fourth request skip + numRecords = 1700 which is equal to the total records so the loop should stop.

And after we have all of those arrays we somehow merge them together into a single table which is way more advanced javascript then what I am familiar with.

Answer 1

Your thinking is on the right track. The API indicates to use the skip feature in a request based on the totalRecords and numRecords values that are in the response metadata.

To set this up you'll need three parts.
First off, your headers and body. The headers will remain unchanged as they need to be the same for each request.

The body will get skip value, but this value is different for each request, so we'll add that part when we make the request.

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

const body = {
  "from": "bq9dajvu5",
  "select": [
    15,
    8,
    50,
    48,
    19
  ],
  "where": `{25.EX.${rid}}`,
  "sortBy": [
    {
      "fieldId": 50,
      "order": "ASC"
    },
    {
      "fieldId": 8,
      "order": "ASC"
    }
  ] // options object will be added later.
};

The second part is rewriting your request script so that we can pass a skip value and put that in the body of the request. I did see you using XMLHttpRequest(), but I'll recommend looking into the newer Fetch API. It's basically the same, but with a different, and in my opinion, more readable syntax.

Because the skip value is dynamic we build up body of the request by combining the properties of the body object, with the options property, which holds the skip property and value.

/**
 * Makes a single request to the records/query endpoint.
 * Expects a JSON response.
 * 
 * @param {number} [skip=0] Amount of records to skip in the request.
 * @returns {any}
 */
const getRecords = async (skip = 0) => {
  const url = 'https://api.quickbase.com/v1/records/query';

  // Make the request with the skip value included.
  const response = await fetch(url, {
    method: 'POST',
    headers,
    body: JSON.stringify({
      ...body,
      "options": {
        "skip": skip
      }
    })
  });

  // Check if the response went okay, if not, throw an error.
  if (!response.ok) {
    throw new Error(`
      The getRecords request has failed: 
      ${response.status} - ${response.statusText}
    `); 
  }

  // Decode the body of the response
  const payload = await response.json();
  return payload;
};

The last part is about making sure that the getRecords function keeps getting called if more records are needed from the API.

To do this, I've created a recursive function, meaning that it will keep calling itself until a condition is met. In this case, we'll want to keep calling the function until there are no more records to get.

Whenever there is no more request to make, it will return an object, that is similar to the original response, but with all data arrays combined.

So that means you'll have the same structure and don't have to do any additional stuff to flatten or restructure the arrays to create the table.

/**
 * Recursive function which keeps getting more records if the current amount
 * of records is below the total. Then skips the amount already received
 * for each new request, collecting all data in a single object.
 * 
 * @param   {number} amountToSkip Amount of records to skip.
 * @param   {object} collection The collection object.
 * @returns {object} An object will all data collected.
 */
const collectRecords = async (amountToSkip = 0, collection = { data: [], fields: [] }) => {
  try {
    const { data, fields, metadata } = await getRecords(amountToSkip);
    const { numRecords, totalRecords, skip } = metadata;

    // The amount of collected records.
    const recordsCollected = numRecords + skip;

    // The data array should be merged with the previous ones.
    collection.data = [
      ...collection.data,
      ...data
    ];

    // Set the fields the first time. 
    // They'll never change and only need to be set once.
    if (!collection.fields.length) {
      collection.fields = fields;
    }

    // The metadata is updated for each request.
    // It might be useful to know the state of the last request.
    collection.metadata = metadata;
    
    // Get more records if the current amount of records + the skip amount is lower than the total.
    if (recordsCollected < totalRecords) {
      return collectRecords(recordsCollected, collection);
    }

    return collection;
  } catch (error) {
    console.error(error);
  }
};

Now to use it, you call the collectRecords function which will then in turn keep making requests until there is nothing more to request. This function will return a Promise, so you'll have to use the then method of a Promise to tell what you want to do whenever all records have been retrieved.

It's like waiting for everything to finish and then do something with the data.

// Select the table div element.
const tableDiv = document.getElementById('line_items_div');

// Get the records, collect them in multiple requests, and generate a table from the data.
collectRecords().then(records => {
  const data = transformRecordsData(records);
  const table = createTable(data);
  tableDiv.append(table);
});