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.

score 1 · Answer 1 · answered Nov 09 '22 at 08:32

I tried to reproduce your scenario in my environment and getting similar result with next links.

Sample data with next links: enter image description here

To work around this you can use Mapping Option and import the schema of the json output. enter image description here

Then delete the unwanted Sections: enter image description here

Edit the destination column json path and then Copy it. document for reference

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

1 Answers1