REST API pagination loop and append json body responses

[michaelshparber]

I have created an HTTP GET request to an api source and I am successfully getting the "next page url"

How do I loop through the pages and "append" all the json body responses?

I need smth like "Do... Until" there is no "next" response, but how do I append the response bodies?

At the end I need either to save json file that contains all the pages responses or to parse it to SQL table.

Thank you!

[abm]

Hi @michaelshparber 

First declare two variables. See below.

Next we need a Do Until loop

Finally outside the loop you can get the full appended JSON text stored under FinalJSONText.

Also if your Do until is getting timed out then click the Change limits link under the Do Until action step and increase the time limit.

Thanks

[michaelshparber]

Great, @abm , it is working!

I've previously tried to use Array variable to store the body responses, but it failed.

I also looked at Object variable - but could not find the append action...

Never thought to use String variable...

I have a follow-up question - is string variable "strong" enough to store very long json responses?

Maybe it is still better to somehow parse my body responses and store them in some array?

I also need to save these results to one single .json file - how do I do it?

Thank you very much!

[abm]

Hi @michaelshparber 

variable max size is 104857600 bytes.

Array could be an option and use the append to array action to construct the full JSON.

Thanks

[michaelshparber]

I tried to save the string as json file, but it adds brackets [] at the beginning and the end of each loop

so it is not a valid json.

Please help

[yeamans]

@abm 

In the variable2, what's the formula used for false? 

In my json I have a string called "records": 864 with a max per page limit of 100. 

Would you be able to help me figure out what I need to enter? 

[abm]

Hi @yeamans 

It all depends on your Get Items action record how you filtering? Are you using the pagination technique to get every 100 records? Then after that check if any records are returned or not? If no records then set to flag to true and under the else you can increment the pagination counter for your next get items iteration. I need to see the full flow and the steps then only I can give you an accurate answer.

Thanks

[yeamans]

@abm 

Thanks for responding. Here's screenshots of my flow and the body output from the HTTP request.  I don't see  any referencing what page it's on, only a total number of records. 

I was told to use query "skip" and & "take" to ensure I get 100 records per call, then use the skip to go to the next 100.

{
  "statusCode": 200,
  "headers": {
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "Request-Context": "appId=cid-v1:624aed6e-f9f0-45c4-a9da-233ca4d7167a",
    "X-RateLimit-Limit": "60",
    "X-RateLimit-Remaining": "59",
    "X-RateLimit-Reset": "1613169209",
    "X-Robots-Tag": "noindex, nofollow, none, noimageindex, noarchive, nocache, nosnippet, noydir",
    "Date": "Fri, 12 Feb 2021 22:32:29 GMT",
    "Content-Type": "application/json; charset=utf-8",
    "Content-Length": "91176"
  },
  "body": {
    "items": [
      {
        "itemNumber": "123456789",
        "serial": "XXXXX",
        "manufacturer": "manufacturer",
        "importer": null,
        "model": "M123456",
        "type": "Pistol",
        "caliber": "9MM",
        "location": "Texas",
        "condition": null,
        "mpn": null,
        "upc": null,
        "barrelLength": null,
        "overallLength": null,
        "acquisitionType": "Transfer",
        "acquire_Date": "2017-09-07T00:00:00",
        "acquisitionContactId": "df671301-a029-4ffd-9241-a7e7015d3507",
        "dispositionType": "IN CUSTODY",
        "dispose_Date": "2020-06-02T16:00:50",
        "dispositionContactId": "07bf8219-62e3-4584-a6d7-abd00101b0a8",
        "deleted": false,
        "disposed": true,
        "isTheftLoss": false,
        "isDestroyed": false,
        "doNotDispose": false,
        "price": null,
        "cost": null,
        "id": "1b934518-aa5c-4961-9d7f-a7e7015d3507",
        "externalId": null,
        "status": {
          "id": 3,
          "name": "Disposed"
        },
        "dispositionId": "fc7b0b0b-fb8f-4c59-9753-abd000ffef8c",
        "dispositionExternalId": null,
        "createUtc": "2017-08-26T04:07:22.9686315",
        "updateUtc": "2020-06-04T15:39:56.9456672",
        "quickCodeUrl": "https://gunqr.co/g/GEWTG1yqYUmdf6fnAV01Bw",
        "attachments": [
          {
            "attachmentId": "ec7ddad9-347b-4dcb-b3b7-abcf0142234b",
            "fileName": "Condition.png",
            "description": null,
            "date": "2020-06-03T14:32:52.0855198",
            "downloadUrl": "https://cloud.fastbound.com/xxxxxxx/api/Attachments/Download/ec7ddad9-347b-4dcb-b3b7-abcf0142234b"
          }
        ],
        "notes": [
          {
            "date": "2020-06-03T14:26:52.4977767",
            "type": "Item",
            "text": "Verified: <Date>, Address: <Address>"
          }
        ]
      }
    ],
    "records": 846
  }
}

 

[abm]

Hi @yeamans 

My first reply thread (see the figure) showed the logic we discussed. The expression length(body('Get_items')?['items']) returns the total number of items retrieved. This will decide the logic of the Do Until loop flag IsNoMoreRecords.

Thanks

[Automate123]

Hey @abm 

I have tried to apply your login into my flow to enable pagination and therefore return more than 100 Jira issues. My flow can be seen below:

Could you help with what expression to add into the condition connector?

From reading the thread I see you recommended

length(body('Get_items')?['items'])

However I am getting errors, so I tried changing the expression to match my JSON as it returns issues to

length(body('Get_issues')?['issues'])

And am still getting the error message when trying to save and test the flow:

Sample of my JSON returned from my REST API call:

{
"expand": "schema,names",
"startAt": 0,
"maxResults": 100,
"total": 142,
"issues": [{ LIST OF ISSUES

Any advice would be apprecaiated,

Thanks

[yeamans]

@Automate123 

Inside the HTTP body, you'll need to determine the string that will be used to pagination the condition. 

Are you able to provide a sample of the HTTP output? This will help identify that string. 

[Automate123]

Hi @yeamans 

Thank you for the response.

Please find a sample of the HTTP output below:

{

  "statusCode": 200,

  "headers": {

    "vary": "Accept-Encoding",

    "Strict-Transport-Security": "md",

    "ATL-TraceId": "07b",

    "x-arequestid": "ac6a",

    "x-aaccountid": "6000ad1",

    "X-XSS-Protection": "1; lock",

    "Transfer-Encoding": "chked",

    "timing-allow-origin": "*",

    "x-envoy-upstream-service-time": "369",

    "X-Content-Type-Options": "nof",

    "Connection": "keep-alive",

    "Expect-CT": "report-uri=\"http\", enforce, max-age=86400",

    "Cache-Control": "no-",

    "Date": "Mon, T",

    "Set-Cookie": "at7ae388cb0dc82fa8421573fb66d07_lin; Path=/; Secure",

    "Server": "AtlassianP8.1",

    "Content-Type": "appli",

    "Content-Length": "1283347"

  },

  "body": {

    "expand": "schema,names",

    "startAt": 0,

    "maxResults": 100,

    "total": 148,

    "issues": [{

      "expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",

      "id": "28361",

      "self": "i/3/issue/28361",

      "key": "SR-1212121",

      "fields": {

        "statuscategorychangedate": "2021-05-03T12:06:10.073+0100",

        "fixVersions": [],

        "customfield_10750": null,

        "resolution": null,

        "customfield_10751": null,

        "customfield_10752": null,