I have an API call where I am getting a list of customers from a site and a transformer that is filtering to only include relevant data for each customer:
This is a picture of my transformer response. Now I will open the first customer:
As shown in the picture, this customer has an ID of "1".
I have a SQL table that has an ID column:
For each customer, I would like to map the ID of that customer from the API response into a new row in my SQL table. How can this be done?
I think a SQL JSON query would do that you need here.
https://docs.retool.com/docs/querying-via-sql#joining-two-json-arrays
Select sqlCustomers.customer_name, apiCustomers .DisplayName from {{qryCustomers.data}} as sqlCustomers
join {{restCustomers.data}} as apiCustomers on sqlCustomers.id=apiCustomers.Id
One prereq is to transform your query into an array (return formatDataAsArray(data)). It looks like your API results are in the right format already.
Thank you for the response, however, this does not seem like it creates a new row in the SQL table for each object in my array.
Ah, I missed the part about the new row.
In that case just use a Bulk Insert query. You will need to create a transformer that will create an array of objects with properties that have the same names as your table. Pass that array into the Array of records to insert setting.
Here are some references to get you started:
Thank you so much!