Getting the data from a REST API into the right table structure

Queries and Resources
1

Hi there,

A newbie here just starting with Retool. I created a few tables now which were quite easy to fill with data but now I got stuck with a response from an API with the data below:

{
"data": {
"120898": {
"2023-01-01": {
"price": 140,
"min_length_of_stay": 1,
"available": 0
},
"2023-01-02": {
"price": 140,
"min_length_of_stay": 1,
"available": 0
},
"2023-01-03": {
"price": 140,
"min_length_of_stay": 1,
"available": 1
},
"2023-01-04": {
"price": 140,
"min_length_of_stay": 1,
"available": 0
},
"2023-01-05": {
"price": 140,
"min_length_of_stay": 1,
"available": 0
},
"2023-01-06": {
"price": 140,
"min_length_of_stay": 1,
"available": 1
},
"2023-01-07": {
"price": 140,
"min_length_of_stay": 1,
"available": 0
},
etc etc.

I would like to add this data to a table and have tried a lot of different ways to fill the 'data' field of the table but none of them seems to have the desired result.

I would like to have a table with rows starting with the ID (120898) then the data, price etc all in seperate columns.

If anyone could help me with a fitting solution that would be great!

thnx

Frans

2

Hello @Industryofcool

This is the structure of your data

You would need to enable and use a query transformer, to edit it into a structure that can be accepted by the table.

The table accepts an Object of Arrays or an Array of Objects

In this case you can modify your data and transform it to an array of Objects

1658×906 145 KB

I have added a sample code to get you going.

\

3

Thank you very much for your help! This looks like a great solution. In your Screenshot it seems the transformer1 changes the output of the API to the desired format. Only in the part of the screenshot with the arrow it seems like I'm seeing the code for query3. Could you show me the code used for the transformer1? Or am I interpreting this wrong?

Thanks again for your help!