locked
How to flatten JSON data from a REST API after pagination in Data Factory RRS feed

  • Question

  • Using Data Factory to ingest a REST API and unrolling the pagination (relying on the built-in facilities within the REST dataset) might easily get you deeply nested arrays.

    - A single object itself within the result might contain an array

    - In case a REST API returns multiple objects and supports pagination, the objects are returned in an array

    - The Azure Data Factory pagination support furthermore places each "page" in an array element

    This results in three levels of nested arrays such as this:

    [
      {
        "data": [
          {
            "device": "Chassis",
            "sensors": [
              {
                "id": 1
              },
              {
                "id": 2
              },
              {
                "id": 3
              }
            ]
          },
          {
            "device": "Hull",
            "sensors": [
              {
                "id": 4
              },
              {
                "id": 5
              }
            ]
          }
        ],
        "paging": {
          "next": "https://api.example.org/device?page=1"
        }
      },
      {
        "data": [
          {
            "device": "Engine",
            "sensors": [
              {
                "id": 6
              },
              {
                "id": 7
              }
            ]
          },
          {
            "device": "Component A",
            "sensors": [
              {
                "id": 8
              },
              {
                "id": 9
              },
              {
                "id": 10
              }
            ]
          }
        ],
        "paging": {
          "previous": "https://api.example.org/device?page=0",
          "next": "https://api.example.org/device?page=2"
        }
      },
      {
        "data": [
          {
            "device": "Component B",
            "sensors": [
              {
                "id": 11
              },
              {
                "id": 12
              }
            ]
          },
          {
            "device": "Component C",
            "sensors": [
              {
                "id": 13
              },
              {
                "id": 14
              }
            ]
          }
        ],
        "paging": {
          "previous": "https://api.example.org/device?page=1",
          "next": "https://api.example.org/device?page=3"
        }
      },
      {
        "data": [
          {
            "device": "Component D",
            "sensors": [
              {
                "id": 15
              },
              {
                "id": 16
              }
            ]
          },
          {
            "device": "Component E",
            "sensors": [
              {
                "id": 17
              },
              {
                "id": 18
              }
            ]
          }
        ],
        "paging": {
          "previous": "https://api.example.org/device?page=2"
        }
      }
    ]

    What is the proper way to flatten such input (device and sensors in the example) while simultaneously getting rid of the superfluous paging elements? Preferably still providing the output in JSON. Is this possible?


    • Edited by vjraitila Sunday, October 27, 2019 1:50 PM
    Sunday, October 27, 2019 8:51 AM

All replies

  • This is a translation problem which you have to use dataflow.
    • Proposed as answer by dataflowuser Monday, October 28, 2019 6:04 AM
    Monday, October 28, 2019 3:16 AM
  • Hi vjraitila,

    For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket, else could you please send an email to AzCommunity@Microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter. 

    MSDN Thread URL:https://social.msdn.microsoft.com/Forums/en-US/a558bd8f-e88f-4c9f-8022-74d3da489104/how-to-flatten-json-data-from-a-rest-api-after-pagination-in-data-factory?forum=AzureDataFactory

    Email subject: <ADF: How to flatten JSON data from a REST API after pagination in Data Factory>

    Subscription ID: <>

    Please let me know once you have done the same


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, October 31, 2019 10:05 PM
  • Hi there,

    Checking to see if you have a got chance to email the details as requested in the previous comment. Please do let us know here once the email is sent.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, November 1, 2019 6:38 PM
  • Hi there,

    Just checking to see if you still need assistance on you query? If so could you please share the details requested in my previous comment.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, November 4, 2019 6:34 PM