Azure Data Factory JSON syntax

Question

In Azure Data Factory, I have a copy activity. The data source is the response body from a REST API POST request.

The sink is a SQL table. The problem is that, even though my JSON data contains multiple rows, only the first row is getting copied.

The source data looks like the following:

{
    "offset": 0,
    "limit": 1000,
    "total": 65,
    "loaded": 34,
    "unloaded": 31,
    "cubeCaches": [
        {
            "id": "MxMUVDN0Q1MzAk5MDg6RDkxREQxMUU5RDBDNzR2NMTk6YWNsZGxwMTJtc3QuY2952aXppZW50aW5==",
            "projectId": "15D91DD11E9D0C74B3319",
            "source": {
                "name": "12302021",
                "id": "07EF95111EC7F954158",
                "type": "cube"
            },
            "state": {
                "active": true,
                "dirty": false,
                "infoDirty": false,
                "persisted": true,
                "processing": false,
                "loadedState": "loaded"
            },
            "lastUpdateTime": "2022-01-24T14:22:30Z",
            "lastHitTime": "2022-02-14T20:02:02Z",
            "hitCount": 1,
            "size": 798720,
            "creatorId": "D4E8BFD56085",
            "lastUpdateJob": 18937,
            "openViewCount": 0,
            "creationTime": "2022-01-24T15:07:24Z",
            "historicHitCount": 22,
            "dataLanguages": [],
            "rowCount": 2726,
            "columnCount": 9
        },
        {
            "id": "UYwMTIxMUFNjkxMUU5RDBDMTRCNkMwMDgwRUYzNUQ0MUI6YWNsZjLmNvbQ==",
            "projectId": "120D0C1480EF35D41B",
            "source": {
                "name": "All Clients (YTD)",
                "id": "49E5B13466251CD0B54E8F",
                "type": "cube"
            },
            "state": {
                "active": true,
                "dirty": false,
                "infoDirty": false,
                "persisted": true,
                "processing": false,
                "loadedState": "loaded"
            },
            "lastUpdateTime": "2022-01-03T01:00:01Z",
            "hitCount": 0,
            "size": 82488152,
            "creatorId": "1E2AFB011E80EF35FF14",
            "lastUpdateJob": 364091,
            "openViewCount": 0,
            "creationTime": "2022-02-14T01:04:55Z",
            "historicHitCount": 0,
            "dataLanguages": [],
            "rowCount": 8146903,
            "columnCount": 13
        }
}

I want to add a row in the Sink table (SQL) for every "id" in the JSON. However, when I run the activity, only the first record gets copied. It's mapped correctly, but I want it to copy all rows in the JSON, not just 1.

My Mapping tab in Azure Data Factory looks like this:

What am I doing wrong here? I'm thinking there is something wrong with my "Source" syntax for each of the columns...

Answer 1

In $cubeCashes[0][...] you're explicitly mapping the first element from this array into columns, and that's why only one row lands in the Sink.

I don’t know a way to achieve what you intend with copy activity only. I would use the Mapping Data Flow here, and inlide I would flatten (Flatten activity) your data to get the array of objects.

Then from this flattened dataset you could use a Derived Column to map the fields in JSON into columns of your target, Select, to remove unwanted original fields, and Sink it into your target location.