Azure Data Factory - API extraction and New column

user_17337687 0
Viewed 45

I need to extract data from an API to Azure and the API output is like this:

[{"ID":0,"SourceIndex":437,"ValueName":""},
{"ID":1,"SourceIndex":438,"ValueName":"CPSA21"},
{"ID":2,"SourceIndex":439,"ValueName":"CPSA21"},
{"ID":3,"SourceIndex":440,"ValueName":"MLPDS5"},
{"ID":4,"SourceIndex":441,"ValueName":"LEOD40"},
{"ID":5,"SourceIndex":442,"ValueName":"MCN312"}]
[1234567,
[531,65,0,12,19,3]

The goal is to create a new object named "Value" with the values found in the last line of the output and write to a file. Expected output:

ID SourceIndex ValueName Value
0 437 531
1 438 CPSA21 65
2 439 CPSA21 0
3 440 MLPDS5 12
4 441 LEOD40 19
5 442 MCN312 3

Is this possible to achieve using Azure Data Factory and how? Or would another solution be better? Thanks

data-transform azure-data-factory api arrays json
1 Answers

Here is a demo that i built for your use-case.

First i created a Json file containing your data like so: enter image description here

The main idea is to join the array with the Json data and add a new key as you requested, this can be done by adding a derived column.

ADF:

  1. created a dataflow.
  2. set a parameter in dataflow constantValues : [531,65,0,12,19,3]
  3. in derived column added the value column with the corresponding value : $constantValues[ID + 1] (the idea is to match id = 0 with the first value in the array).
  4. saved to cached sink.

Parameter in pipeline: enter image description here

Derived Column: enter image description here

Output: enter image description here

Please check this link: https://docs.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

user_67888850
Related