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

Question

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']:

(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.

Answer 1

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.