Can I use id's/numbers to map JSON elements in the ADF copy data activity?

Dirk Sachse 106 Reputation points
2022-03-30T07:29:47.823+00:00

Hello,
I made a pipeline to get data from a REST API.
This is a part of the response I am struggeling with:

   },
    "_links": {
        "self": {
            "href": "https://api.com/rest/v1/mailings?afterId=0&pageSize=1000"
        },
        "next": {
            "href": "https://api.com/rest/v1/mailings?afterId=4652&pageSize=1000"
        },
        "curies": [
            {
                "href": "https://apidocs.com/xpro/rest/v1/relations/{rel}",
                "name": "inx",
                "templated": true
            }
        ]
    }

This is the mapping in the copy data activity:

                            "source": {
                                "path": "$['_links']['self']['href']"
                            },
                            "sink": {
                                "name": "thisIdLink"
                            }
                        },
                        {
                            "source": {
                                "path": "$['_links']['next']['href']"
                            },
                            "sink": {
                                "name": "nextIdLink"
                            }
                        }

This works fine until I get to the last data frame. I run the REST request several times, because I get only 1000 records per call.

The response of the last call looks like this:

},
"_links": {
    "self": {
        "href": "https://api.com/rest/v1/mailings?afterId=4652&pageSize=1000"
    },
    "first": {
        "href": "https://api.com/rest/v1/mailings?afterId=0&pageSize=1000"
    },
    "curies": [
        {
            "href": "https://api.com/xpro/rest/v1/relations/{rel}",
            "name": "inx",
            "templated": true
        }
    ]
}

The name of the element "next" changes to "first", like this: "$['_links']['next']['href']" -> "$['_links']['first']['href']"

Is it possible to use the element ID in the mapping?
Instead of "$['_links']['next']['href']", I want to use something like this "$['_links'][1]['href']"

What would be the correct syntax?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,529 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.