Update DB from array

Workflows
1

Hello All,

I was hoping to get some help with a workflow issue I am running into.

The first step of the workflow is to query my RetoolDB to get all the access_tokens which we will call query1. Below is the response:

{"data":[{"access_token":"123"},{"access_token":"456"},{"access_token":"789"},{"access_token":"012"},{"access_token":"345"},{"access_token":"678"},{"access_token":"901"}

After that I want to query an API endpoint using each access_token which we will call query2. I cannot use the loop function because the query times out even after increasing the timeout amount, so what I did was create a separate query for each access_token which works fine for me. The response from one of the access_tokens returns with an array which includes an account_id and institution_id like this:

{"data":{"accounts":[{"account_id":"123456789","balances":{"available":123,"current":123,"iso_currency_code":"USD","limit":null,"unofficial_currency_code":null},"mask":"2222","name":"Bank Basic Business Checking","official_name":"Bank Basic Business Checking","subtype":"checking","type":"depository"},{"account_id":"987654321","balances":{"available":456,"current":456,"iso_currency_code":"USD","limit":null,"unofficial_currency_code":null},"mask":"3333","name":"Bank Basic Business Checking","official_name":"Bank Basic Business Checking","subtype":"checking","type":"depository"}],"item":{"available_products":["balance"],"billed_products":["transactions"],"consent_expiration_time":null,"error":null,"institution_id":"ins_09","item_id":"Pr4YP","products":["transactions"],"update_type":"background","webhook":""},"request_id":"saNM"}}

The final step is where I want to insert a record into my RetoolDB with the access_token from query1, and then the associated numerous account_id and institution_id from query2. How can I achieve that?

Any help would be appreciated.

Thanks

2

Are you asking how to do it in Retool, or in general?
In general, it really depends on your DB table structure, in terms of how you want to enter it back in. It looks like your accounts array has some nested objects (balances has a few properties beneath it, are those consistent or variable?) If you have a table row for each item you want to save, you'd need to reshape the data to be an array of objects with key:value pairs matching the columns. You could also simple make a table with an access_token column (text) and a data (json) and just store the JSON data in there directly. It depends mainly on what you need to do with the data.

If you're asking how to get the access token into the result from query 2, you could just add a code node after the query and return merged data. For example:

  1. You have a node that feeds into your query, it contains the access_token, we'll call that node atData. It's value is {access_token: "123"}.
  2. Next is your query, call it query1 and you've described it's data above.
  3. Create a new code node mergeData and do something like this:
//Get the access token
const accessToken = asData.data.access_token
// Get the query data
const queryData = query1.data
// Create a new variable with both
const allData = {accessToken, queryData}
//Return it
return allData

Now you can access mergeData.data and it would look like:

{
  accessToken: "123",
  queryData: [{"account_id":"123456789","balances":{"available":123,"current":123...]
}
1 Like
3

Thank you!!