How to aggregate JSON items when the parent object is dynamic?

Hi!

So, I am having a real trouble with an API request.
It returns my data with pagination and the parent object always change according to the property code, as showed in the JSON below.

I was able to run the request per page using a repeater but the array agreggator does not aggregate the values. I need to send this data to a spreadsheet as the headers being what is inside of the property codes. Any ideas how I can do this?

[
    {
        "statusCode": 200,
        "headers": [
            {
                "name": "content-type",
                "value": "application/json"
            },
        ],
        "data": {
            "1761": {
                "Code": "1761",
                "Category": "Apartament",
                "City": "Chapecó",
                "Value": "9660",
                "Rooms": "0",
            },
            "5079": {
                "Code": "5079",
                "Category": "House",
                "City": "Chapecó",
                "Value": "1000",
                "Rooms": "2",
            },
            "total": 1949,
            "total_pages": 39,
            "page": 1,
            "quantity": 50
        },
        "fileSize": 12516
    }
]

Hello, @Bruno_Gabriel , I share a possible solution to the case you raise.
I did a simulation with the data you sent as an example.

Please tell me if this is what you are looking to do

Scenario execution

Data entered

Records created in the spreadsheet
image

The keys of the object are obtained and the keys that are from the pagination are eliminated.

The elements of the array are iterated.

A data structure is created to insert the records in the spreadsheet.
Note that each record in the spreadsheet is made up of the character string [] and within each value to be inserted in the columns separated by commas.

Records are inserted in bulk.

2 Likes

Hey @Francisco_Fontes! Thanks for the help.

I am trying to adapt your scenario to get all the data that I need from all the pages, but no success. Any idea how I can run thought every page and then agreggate this values to iterate the data?

Here what I tried to do.

Hi @Bruno_Gabriel , the API you consume is an open API? what api is it? I need additional information to help you.

2 Likes

@Francisco_Fontes, it is an API of a private system.

In the end of the request, it will return the number of pages my request possesses.
Each page contains all the property codes that I need to filter.

Below, it has 39 pages that I need to agreggate to then apply to the scenario that you helped me to create. I inform the page number in my URL (page=)

,
            "total": 1949,
            "total_pages": 39,
            "page": 1,
            "quantity": 50
        },
        "fileSize": 12516
    }
]

So what you want to do is go through all the pages and then do the insert, right?

2 Likes

@Francisco_Fontes Yes! If the API did not have pagination, it would be easier! :joy:

I was able to do the aggregation, separed as bundles but in the end it does not filter the data.

What you can do is, for each page, which has a certain number of records, do the bulk insert.
From what I see, you have 39 pages and 1949 records; that is, you would be inserting that number of records in 39 operations in the Google spreadsheet.

I share the example I just did.
In my example it has 50 pages.

2 Likes

It worked, thanks!

I was complicating things too much.

If it has worked for you, I invite you to mark the publication as solved.