topic Re: Pagination results in poorly-formed JSON in Pipelines

Pagination results in poorly-formed JSON

menchgelof — Thu, 18 Jul 2024 01:58:49 GMT

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.

Re: Pagination results in poorly-formed JSON

Anonymous — Thu, 18 Jul 2024 10:11:29 GMT

Hi menchgelof ,

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

Best regards,

Adamk Kong

Re: Pagination results in poorly-formed JSON

menchgelof — Tue, 23 Jul 2024 17:27:49 GMT

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.

Re: Pagination results in poorly-formed JSON

kubas — Wed, 26 Mar 2025 13:03:10 GMT

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

Re: Pagination results in poorly-formed JSON

menchgelof — Wed, 26 Mar 2025 17:33:21 GMT

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.

Re: Pagination results in poorly-formed JSON

kubas — Thu, 27 Mar 2025 10:47:56 GMT

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

Re: Pagination results in poorly-formed JSON

menchgelof — Thu, 27 Mar 2025 13:26:47 GMT

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