We have an unusually formatted JSON file to convert into a table format using Microsoft Azure Data Factory (ADF). I’ve had a few attempts using the flatten and aggregate components, but haven’t been able to crack it yet, so hence am opening the problem you guys.

Here is a sample of the JSON data, which is Currency Exchange Rate data from XE.com, output from their “monthly_average” API.

{
    "from": "GBP",
    "amount": 1.0,
    "year": 2024,
    "to": {
        "AUD": [
            {
                "monthlyAverage": 1.9137229984,
                "month": 6,
                "daysInMonth": 30
            }
        ],
        "CAD": [
            {
                "monthlyAverage": 1.7413289475,
                "month": 6,
                "daysInMonth": 30
            }
        ],
        "EUR": [
            {
                "monthlyAverage": 1.1805731526,
                "month": 6,
                "daysInMonth": 30
            }
        ],
        "USD": [
            {
                "monthlyAverage": 1.2709593559,
                "month": 6,
                "daysInMonth": 30
            }
        ]
    }
}

I’d like to convert the JSON to a table like this.. The list of “from” and “to” currencies are variable so I’d not want to hard-code the currency codes in the solution.

from  to    monthlyaverage
------------------------
GBP   AUD   1.9137229984
GBP   CAD   1.7413289475
GBP   EUR   1.1805731526
GBP   USD   1.2709593559

Many thanks in advance for any advice, tips or more!

SQLSimon.

Nothing to share yet.