CODEWITHSUNDEEP
azure-data-factory
Qianru Song
Qianru Song

Reputation: 341

Split a json string column or flatten transformation in data flow (ADF)

I copy the following csv file to a data flow in ADF.

enter image description here

The column Data has json format, but it is considered string. I want to flatten Data column into individual rows. I tried the flatten transformation, it did not work as Data column is not json. How do I deal with it? I also tried split expression, and it did not work either. Thank you

Upvotes: 0

Views: 2456

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

Just from your screenshot, We can find that :

  1. The data in Data are not JSON format.
  2. Data most look like an Array.
  3. The 'array' has 9 elements.

Me must consider it as the "Array" then we could using Data Flow Derived Column to flatten the Data. Please ref my steps bellow:

Source data: enter image description here

Derived Column expressions and settings:

The expression to make data as string and using index to get the value:

Data 1: split(substring(Data, 2, length(Data)-2), ",")[1]
Data 2: split(substring(Data, 2, length(Data)-2), ",")[2]
Data 3: split(substring(Data, 2, length(Data)-2), ",")[3]
Data 4: split(substring(Data, 2, length(Data)-2), ",")[4]
Data 5: split(substring(Data, 2, length(Data)-2), ",")[5]
Data 6: split(substring(Data, 2, length(Data)-2), ",")[6]
Data 7: split(substring(Data, 2, length(Data)-2), ",")[7]
Data 8: split(substring(Data, 2, length(Data)-2), ",")[8]
Data 9: split(substring(Data, 2, length(Data)-2), ",")[9]

enter image description here

Derived Column output: enter image description here

If the Data are standard JSON format, we need convert the string to JSON first, and then use the key to get the value.

HTH.

Upvotes: 2

Related Questions