Getting data from REST API in Synapse with pagination rules giving invalid JSON structure

Valentin Duhamel 11 Reputation points
2022-11-03T11:33:16.38+00:00

I'm getting data from SAP Success Factors using the REST API in Azure Synapse Analytics with Copy data activity. Below the structure of the body returned by the REST api:

{  
    "d": {  
        "results": [  
            {  
                "object1": "test1"  
            },  
            {  
                "object2": "test2"  
            }  
        ],  
        "__next": "mynexturl"  
    }  
}  

I set the pagination rules to: ['d']['__next']:
7S8Ad.png

(I'm precising that if I put a $ in the pagination rules: $['d']['__next'] or $.d.__next according to the official MS documentation, I'm getting only the first 1000 rows).

With this I'm getting all data but it doesn't concatenate all objects in the "results" node, instead it's just appending the entire structure:

{  
    "d": {  
        "results": [  
            {  
                "object1": "test1"  
            },  
            {  
                "object2": "test2"  
            }  
        ],  
        "__next": "mynexturl"  
    }  
}  
{  
    "d": {  
        "results": [  
            {  
                "object3": "test3"  
            },  
            {  
                "object4": "test4"  
            }  
        ],  
        "__next": "mynexturl"  
    }  
}  

While I'm trying to get this:

{  
    "d": {  
        "results": [  
            {  
                "object1": "test1"  
            },  
            {  
                "object2": "test2"  
            },  
            {  
                "object3": "test3"  
            },  
            {  
                "object4": "test4"  
            }  
        ],  
        "__next": "mynexturl"  
    }  
}  

I would like to avoid an approach with a foreach loop that would write multiple json and then merge them into one.

Thanks for your help.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,565 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,933 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,311 Reputation points Microsoft Employee
    2022-11-07T14:01:54.203+00:00

    Hi @Valentin Duhamel ,

    Thank you for posting query in Microsoft Q&A Platform.

    When we use REST connector inside Copy activity and make APIs calls with pagination rules. Then each time whatever the response we get from API it will simply get appended. We cannot control the responses to get merged in previous response at some particular node.

    Hence you see data that way getting appended.

    We need to either process this entire JSON after copy activity as per our needs using dataflows or to make API call and foreach on items and merge them into one.

    Hope this helps. Please let me know how it goes.