How can I implement Logic App HTTP Connector paging through results using Offset and Limit

Question

Hi,

I have a requirement to fetch information from an API using an HTTP connector, but there is a hard limit of 30 on the output. The API offers an offset option, so I need to make use of it and fetch all the records, storing them to a file on ADLS Gen2. What is the best way to accomplish this?

Answer 1

Hi Vivek Komarla Bhaskar

Thanks for reaching out on Microsoft Q&A!

You can do this with a 'Do until', where you update the offset after each run with the limit. This makes that you retrieve "pages" of 30 items every run.

So, what you need at the start:

• An integer variable called 'limit' of 30
  
• An integer variable called 'offset' with an initial value of 0
  

Next we're going to retrieve the first set of data, based on the newly created variables. This set is stored in a new array variable called 'currentRecords', which is a sort of temporary hold for the current records in a run.
Also we're counting the number of records using the length() expression and store it in a new integer variable called 'currentRecordCount':

@{length(variables('currentRecords'))}

And store the current records in a new array variable 'allRecords' (holding all records from all runs).

Now it's time to dive in the 'Do until' and set the escape condition to check whether the variable 'currentRecordCount' is less then 'limit', because when less then 30 records are responded we're at the end of the sets.
Within the 'Do until' we're going to increment 'offset' with the value of 'limit', so we start with an offset of 30, retrieving the next 30 records.

Then we're calling the endpoint again to retrieve the next set:

This is stored together with the values from the 'allRecords' in the 'currentRecords' using:

@{union(variables('allRecords'),body('Get_next_set_of_records'))}

The value of 'currentRecords' in then stored again in 'allRecords' because we're going to use 'currentRecords' again in the next round. Also we're updating the 'currentRecordCount' to check whether or not to escape the 'Do until'.

Lastly, you can process the 'allRecords' array to your liking (in the example through a 'For each' loop.

Please note: This example acts as a guidance, and is not a complete solution in itself. It does provide a way to solve it, but maybe response references etc. need to be adjusted to your case. In my case I made the assumption that the response body is always an array, for example.

You can find the code of this example below:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "For_each_record_in_all_records": {
                "actions": {},
                "foreach": "@variables('allRecords')",
                "runAfter": {
                    "Until_currentRecordCount_is_less_than_limit": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Get_first_set_of_records": {
                "inputs": {
                    "method": "GET",
                    "queries": {
                        "api_key": "YOURAPIKEY",
                        "limit": "@{variables('limit')}",
                        "offset": "@{variables('offset')}"
                    },
                    "uri": "https://www.url.com/api/1.3/authors/search"
                },
                "runAfter": {
                    "Initialize_offset": [
                        "Succeeded"
                    ]
                },
                "type": "Http"
            },
            "Initialize_allRecords": {
                "inputs": {
                    "variables": [
                        {
                            "name": "allRecords",
                            "type": "array",
                            "value": "@variables('currentRecords')"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_currentRecordCount": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_currentRecordCount": {
                "inputs": {
                    "variables": [
                        {
                            "name": "currentRecordCount",
                            "type": "integer",
                            "value": "@length(variables('currentRecords'))"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_currentRecords": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_currentRecords": {
                "inputs": {
                    "variables": [
                        {
                            "name": "currentRecords",
                            "type": "array",
                            "value": "@body('Get_first_set_of_records')"
                        }
                    ]
                },
                "runAfter": {
                    "Get_first_set_of_records": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_limit": {
                "inputs": {
                    "variables": [
                        {
                            "name": "limit",
                            "type": "integer",
                            "value": 30
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Initialize_offset": {
                "inputs": {
                    "variables": [
                        {
                            "name": "offset",
                            "type": "integer",
                            "value": 0
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_limit": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Until_currentRecordCount_is_less_than_limit": {
                "actions": {
                    "Get_next_set_of_records": {
                        "inputs": {
                            "method": "GET",
                            "queries": {
                                "api_key": "YOURAPIKEY",
                                "limit": "@{variables('limit')}",
                                "offset": "@{variables('offset')}"
                            },
                            "uri": "https://www.url.com/api/1.3/authors/search"
                        },
                        "runAfter": {
                            "Increment_offset_with_limit_to_start_on_next_page": [
                                "Succeeded"
                            ]
                        },
                        "type": "Http"
                    },
                    "Increment_offset_with_limit_to_start_on_next_page": {
                        "inputs": {
                            "name": "offset",
                            "value": "@variables('limit')"
                        },
                        "runAfter": {},
                        "type": "IncrementVariable"
                    },
                    "Set_allRecords_to_previous_and_current_set": {
                        "inputs": {
                            "name": "allRecords",
                            "value": "@variables('currentRecords')"
                        },
                        "runAfter": {
                            "Set_currentRecords_to_next_set": [
                                "Succeeded"
                            ]
                        },
                        "type": "SetVariable"
                    },
                    "Set_currentRecordCount_to_next_set": {
                        "inputs": {
                            "name": "currentRecordCount",
                            "value": "@length(variables('currentRecords'))"
                        },
                        "runAfter": {
                            "Set_allRecords_to_previous_and_current_set": [
                                "Succeeded"
                            ]
                        },
                        "type": "SetVariable"
                    },
                    "Set_currentRecords_to_next_set": {
                        "inputs": {
                            "name": "currentRecords",
                            "value": "@union(variables('allRecords'),body('Get_next_set_of_records'))"
                        },
                        "runAfter": {
                            "Get_next_set_of_records": [
                                "Succeeded"
                            ]
                        },
                        "type": "SetVariable"
                    }
                },
                "expression": "@less(variables('currentRecordCount'), variables('limit'))",
                "limit": {
                    "count": 60,
                    "timeout": "PT1H"
                },
                "runAfter": {
                    "Initialize_allRecords": [
                        "Succeeded"
                    ]
                },
                "type": "Until"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "manual": {
                "inputs": {},
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {}
}

Please click 'Accept answer' if you think this is helpful.
Feel free to drop additional queries in the comments below!

Kind regards,

Sonny