'Azure Data Factory JSON Syntax

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

The sink is a SQL table. The problem I'm having 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 ADF 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...

azure-data-factory-2

Solution 1:^[1]

Mate, in $cubeCashes[0][...] you're mapping explicitely the first element from this array into columns, that's why only one row lands in the Sink. I don;t know a way to achievie what you intend with copy activity only, I would use the Mapping Data Flow here, inlide I would flatten (Flatten activity) you data to get the array of objects, then from thios flattened dataset you could use Derived Column to map the fields in json into columns of your target, then Select, to remove unwanted original fields and then Sink it into your target location. It's a late anwer but hope it helps.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Maciej T

'Azure Data Factory JSON Syntax

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]