Pagination results in poorly-formed JSON

[menchgelof]

I have configured a Copy Activity to call paginated results from a REST API. The pagination rules work, calling all pages and assembling them into a single JSON file. However, the output file is poorly-formed JSON. Each page is appended as a list object in the final output file without a comma between them, and there is no set of brackets wrapped around all the page objects, leaving many objects at the root level.

The output file looks like this:

{"page 1 content"}
{"page 2 content"}
{"page 3 content"}
…

I've tried setting the Destination Copy behavior to "Merge files," "Flatten hierarchy," and "Preserve hierarchy." I've also tried changing the File pattern setting to "Array of objects" and "Set of objects." All combinations of settings produce identical files; the settings seem to have no change on the output.

Has anyone experienced this? I'm bewildered on how pagination returns poorly-formed JSON format or what to do about it.

[menchgelof]

@kubas, I believe I had also tried changing the setting for how to structure the JSON without success. Perhaps Microsoft did make a change.

[kubas]

Hi @menchgelof , thanks for your quick answer and suggestions!

I've also tried a little bit with available options and turned out that setting up File pattern help to provide correct JSON format 😊. Weird as I' pretty sure I've tested this option too before and it didn't work.

Nevertheless, seems our problem is resolved in here. You can also try this out in case You would like to use Copy Activity instead of Notebooks

[menchgelof]

@kubas, I gave up on waiting for a fix and moved to using Notebooks to handle paginated API data with a simple if-else loop. Obviously, that's not a solution to the pipeline itself, but it's a way to handle paginated data.

Something like this custom function to assemble the pages and save it all as a variable:

def fetch_paginated_data(base_api_url, endpoint_url, headers, params):
    api_url = base_api_url + endpoint_url  # Concatenate base and endpoint URLs
    all_data = []
    while api_url:
        response = requests.get(api_url, headers=headers, params=params)
        
        if response.status_code == 200:
            data = response.json()
            all_data.extend(data.get('objects', []))  # Collect only the objects
            next_url = data.get('meta', {}).get('next')
            if next_url:
                api_url = base_api_url + next_url  # Construct the full URL for the next page
                params = None  # Reset params after the first request
            else:
                api_url = None
        else:
            print(f"Failed to get data from API. Status code: {response.status_code}")
            break
    return all_data

 I then had to handle formatting the assembled JSON data into well-formed JSON with this bit:

# Convert the collected data to a JSON string
json_data = json.dumps(all_data, separators=(',', ':'))

I then save the JSON data to a file I can save in my lakehouse and use however I need. 

[kubas]

Any updates on that, please? We are also facing the same issue

[menchgelof]

I spent some time with Microsoft Fabric Support today, and we discovered that the preview of the incoming API data (from either the Source or Destination settings) shows proper JSON—meaning that the pages are assembled correctly. They are separated by a comma, and the whole document is wrapped in brackets.

You can see the separation commas in the included screenshot. However, the eventual file output lacks these commas (and document-wrapping brackets). I'm beginning to think this is a bug.

[Anonymous]

Hi @menchgelof ,

I will try to reproduce your needs and try to give a solution. Please be patient.

Best regards,

Adamk Kong