'Flatten two arrays having corresponding values using mapping data flow in azure data factory

I am new to data flows in adf. I have a set of json files in a folder which i would like to parse and flatten two arrays into a csv. The json structure is as follows: enter image description here

I would like to flatten the timestamps and values arrays. Each timestamp in Timestamps array has the corresponding value in the Values array. i.e, value at Timestamps[i] is Values[i]. The output should have a row for each timestamp and corresponding value for a particular label.

enter image description here

Can anyone please help me in achieving this

Thank you!

azureazure-data-factoryazure-data-factory-2azure-data-flow


Solution 1:[1]

To do the correlation between 2 arrays use mapIndex function and merge the arrays together. Then you will use a flatten to get it into relational form.

mapIndex(Timestamps, @( Timestamp = #item, Value = Values[#index]))

Solution 2:[2]

In your data flow, add 2 Flatten transformations and point to the Timestamps and Values arrays in the unroll property in each transformation.

Solution 3:[3]

I know this is rather too late for an answer but I came across this when I was struggling to get the example working. Taking what Kiran wrote and expanding on it, I've written it up as a full end-to-end blog post which is here; it might save others time in future

https://medium.com/kinandcartacreated/consuming-informatica-api-data-with-data-factory-and-data-flows-a3220f6190d3

Solution 4:[4]

You could also do this with Azure Synapse Analytics, serverless SQL pools which supports OPENJSON:

SELECT
    JSON_VALUE( m.value, '$.Id' ) Id,
    JSON_VALUE( m.value, '$.Label' ) Label,
    t.value AS ts,
    v.value AS v
FROM
    OPENROWSET(
        BULK 'https://somestorage.dfs.core.windows.net/datalake/raw/json/metrics.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]
    CROSS APPLY OPENJSON( jsonContent, '$.MetricDataResults' ) m
        CROSS APPLY OPENJSON( m.value, '$.Timestamps' ) t
        CROSS APPLY OPENJSON( m.value, '$.Values' ) v
WHERE t.[key] = v.[key];

My results: enter image description here

It's a valid alternative approach with a pay as you go service.

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 Kiran-MSFT
Solution 2 Mark Kromer MSFT
Solution 3 Gordon Thompson
Solution 4 wBob

Related Questions