Search code examples
azureazure-data-factory

How to pass array of Json as a parameter to Dataflow?

I am getting the output of lookup activity as below.

"value": [ { "PO_id": "XXX", "SO_number": "YYYY" }, { "PO_id": "AAA", "SO_number": "BBBB" } ]

I want to pass this lookup activity output as a paremter to dataflow and convert them into the tabular format

PO_id,SO_number "XXX","YYYY" "AAA","BBBB"

There is no Json[] type in dataflow parameter. I tried with string[] parameter, but getting error

'Array elements must be all of same type'. How can I solve this?

Solution

  • You can pass the Json array as a string to dataflow parameter and then in dataflow use parse transformation to convert them into rows and column.

    • Create a dataflow parameter of type string.

    enter image description here

    • In pipeline, instead of passing the value for dataflow parameter as @activity('Lookup1').output.value , you can pass the value as @string(activity('Lookup1').output.value).

    • In Derived column transformation, add the new column new_col and define the expression for the column as split(replace(replace(replace($parameter1, '[', ''),']',''),'},','}},'),'},')

    enter image description here

    enter image description here

    • Then add a flatten transformation and unroll by the column new_col.
    • Then add the parse transformation. Choose Json as format in parse settings. And then choose single document in Json settings as a document form. In column settings give the below expression
    column: new_col
expression:new_col
Output column type:(name as string,dept as string)

    [Replace name as string,dept as string with required columns and their type]

    gif1

    By this way, you can pass the Json array in the dataflow and parse them as rows and column data.