如何使用复杂的嵌套和未命名数组解析分页的 JSON API 响应? [英] How to parse paginated JSON API response with complex nesting and unnamed array?
问题描述
我已经构建(在@EmielZuurbier 的帮助下)一个发票模板，该模板向 Quickbase 发出 API 调用.API 响应是分页的.如何将分页响应解析为单个表?
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 端点:https://developer.quickbase.com/operation/runQuery
- Quickbase 分页元数据说明:https://developer.quickbase.com/pagination
API 调用的响应如下所示(我删除了数据下的大部分项目，否则在 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
}
}
以下是我正在使用的完整 javascript 代码
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));
这就是我想要实现的(地址仅显示为 xxx，因此该表非常适合 stackoverflow)
| 计数 | 项目编号 | 物业地址 | 完成日期 | 账单代码 | 总工作价格 |
|---|---|---|---|---|---|
| 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 |
我对如何实现这一目标的看法
对于请求公式，我们可能需要一个循环函数，它将跳过一定数量的记录，即 === 到所有 numRecords 的总和，直到 skip + numRecords === totalRecords
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
例如如果 totalRecords = 1700
so for example if the totalRecords = 1700
- 第一个请求
{skip": 0}返回 numRecords=500 - 第二个请求
{skip": 500}返回 numRecords=500 - 第三个请求
{skip": 1000}返回 numRecords=500 - 第四个请求
{skip": 1500}返回 numRecords=200
- First request
{"skip": 0}returns numRecords=500 - Second request
{"skip": 500}returns numRecords=500 - Third request
{"skip": 1000}returns numRecords=500 - Fourth request
{"skip": 1500}returns numRecords=200
在第四个请求 skip + numRecords = 1700 等于总记录，所以循环应该停止.
On the fourth request skip + numRecords = 1700 which is equal to the total records so the loop should stop.
在我们拥有所有这些数组之后，我们以某种方式将它们合并到一个表中，这是比我熟悉的更高级的 JavaScript.
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.
推荐答案
您的想法是正确的.API 根据响应元数据中的 totalRecords 和 numRecords 值指示在请求中使用 skip 功能.
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.
要进行设置，您需要三个部分.
首先，你的 headers 和 body.headers 将保持不变，因为它们对于每个请求都需要相同.
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.
body 会得到 skip 值，但是这个值对于每个请求都是不同的，所以我们会在发出请求的时候加上那部分.
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.
};
第二部分是重写您的请求脚本，以便我们可以传递一个 skip 值并将其放入请求正文中.我确实看到您使用 XMLHttpRequest()，但我建议您查看 较新的 Fetch API.它基本上是一样的，但有不同的，在我看来，更易读的语法.
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.
因为 skip 值是动态的，我们通过结合 body 对象的属性和 来构建请求的 属性，它保存 body>optionsskip 属性和值.
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;
};
最后一部分是关于确保 getRecords 函数在 API 需要更多记录时不断被调用.
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.
每当不再有请求时，它会返回一个对象，该对象类似于原始响应，但所有data 数组组合在一起.
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);
}
};
现在要使用它，您调用 collectRecords 函数，然后该函数将继续发出请求，直到没有更多请求为止.这个函数将返回一个 Promise，所以你必须使用 Promise 的 then 方法来告诉你想要做什么记录已被检索.
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);
});
这篇关于如何使用复杂的嵌套和未命名数组解析分页的 JSON API 响应?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
