Azure Data Factory Nested JSON with titles

Question

While doing a copy data activity from an API call I get roughly the following JSON results.

{
"data": {
    "periods": {
        "1": {
            "period": 1,
            "shortName": "Jan",
            "longName": "Januari"
        },
        "2": {
            "period": 2,
            "shortName": "Feb",
            "longName": "Februari"
        },
        "3": {
            "period": 3,
            "shortName": "Mrt",
            "longName": "Maart"
        },
        "4": {
            "period": 4,
            "shortName": "Apr",
            "longName": "April"
        },
        "5": {
            "period": 5,
            "shortName": "Mei",
            "longName": "Mei"
        },
        "6": {
            "period": 6,
            "shortName": "Jun",
            "longName": "Juni"
        },
        "7": {
            "period": 7,
            "shortName": "Jul",
            "longName": "Juli"
        },
        "8": {
            "period": 8,
            "shortName": "Aug",
            "longName": "Augustus"
        },
        "9": {
            "period": 9,
            "shortName": "Sep",
            "longName": "September"
        },
        "10": {
            "period": 10,
            "shortName": "Okt",
            "longName": "Oktober"
        },
        "11": {
            "period": 11,
            "shortName": "Nov",
            "longName": "November"
        },
        "12": {
            "period": 12,
            "shortName": "Dec",
            "longName": "December"
        }
    }
}

What I want to end up with is a new record for each period. Where the table columns would simply be ['period', 'shortName', 'longName']. I've tried to use the collection reference setting as shown below (and a couple of other configurations just to see if they would work. If I set it up like below I can only set up the first period, period 1, before I of course get duplicate column names throwing errors. So so far I've only managed to properly get the first row written into the database.

I think that if the "1" and so forth werent there, so that it was simply "periods":{ "period": 1, and so forth, the collection reference would work but the extra layer is throwing a curve ball. Is there a way to work around this?

Steve Zhao Steve Zhao · Accepted Answer · 2020-11-13T02:55:44

This JSON format cant achieve your requirement. You need to change your format to this

{
    "data": {
        "periods": [
            {
                "period": 1,
                "shortName": "Jan",
                "longName": "Januari"
            },
            {
                "period": 2,
                "shortName": "Feb",
                "longName": "Februari"
            },
            {
                "period": 3,
                "shortName": "Mrt",
                "longName": "Maart"
            },
            {
                "period": 4,
                "shortName": "Apr",
                "longName": "April"
            },
            {
                "period": 5,
                "shortName": "Mei",
                "longName": "Mei"
            },
            {
                "period": 6,
                "shortName": "Jun",
                "longName": "Juni"
            },
            {
                "period": 7,
                "shortName": "Jul",
                "longName": "Juli"
            },
            {
                "period": 8,
                "shortName": "Aug",
                "longName": "Augustus"
            },
            {
                "period": 9,
                "shortName": "Sep",
                "longName": "September"
            },
            {
                "period": 10,
                "shortName": "Okt",
                "longName": "Oktober"
            },
            {
                "period": 11,
                "shortName": "Nov",
                "longName": "November"
            },
            {
                "period": 12,
                "shortName": "Dec",
                "longName": "December"
            }
        ]
    }
}

and mapping like this:

Azure Data Factory Nested JSON with titles

1 Answers