How to convert data row into valid json in ADF Dataflow

Vivek, Anand 20 Reputation points
2023-01-27T10:48:56.6433333+00:00

I have data in tabular format.

User's image

As part of dataflow transformation want add another derived column like below. Basically converting column data into valid Json string

User's image

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,546 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2023-01-27T14:43:28.6666667+00:00

    Hi Vivek, Anand ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your question , you are trying to create a new json column having key value pair coming out of the other columns. Please correct me if my understanding is wrong.

    You can use aggregate transformation and group by using all 3 columns : 'Column1', 'Column2' and 'Column3' and in aggregate tab, use this expression: JSONVal : 

    collect(@({Column1}={Column1},{Column2}={Column2},{Column3}={Column3}))

    For more details, checkout this video from this point onwards.

    Other alternate way is to use Derived column transformation first to create a new column 'JSONVal' with sub columns as Column1,Column2 and Column3 like mentioned in this video then use aggregate transformation and group by using all 3 columns and use expression : collect(JSONVal) in aggregate tab.

    Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.