在数据流(ADF)中拆分json字符串列或展平转换

问题描述

我将以下csv文件复制到ADF中的数据流.

数据"列具有json格式，但被视为字符串.我想将数据"列展平为单独的行.我尝试了扁平化转换，但由于Data列不是json而不起作用.我该如何处理?我也尝试了拆分表达式，但它也不起作用.谢谢

解决方案

仅从您的屏幕截图中，我们可以发现:

1. Data 中的数据不是JSON格式.
2. 数据最像一个数组.
3. 数组"有9个元素.

我必须将其视为数组".那么我们可以使用数据流

派生的列表达式和设置:

将数据制成字符串并使用索引获取值的表达式:

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

派生列输出:

如果 Data 是标准JSON格式，则需要先将字符串转换为JSON，然后使用密钥获取值.

HTH.

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

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

解决方案

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:

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]

Derived Column output:

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.

这篇关于在数据流(ADF)中拆分json字符串列或展平转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！