Dataflow Derived Columns to parse complex array

Question

can some help me to get the (nth-1) element in below complex arrays in ADF dataflow through derived columns:

"Ranks": {
                "3760901": [
                    6286064,
                    40635,
                    5712864,
                    567,
                    5712962,
                    711,
                    5713354,
                    -1
                ],
                "21377132011": [
                    6351134,
                    1295,
                    6351382,
                    1332,
                    6352064,
                    3212,
                    6580356,
                    -1
                ],
                "9000000000000000000": [
                    6547144,
                    8381,
                    6547914,
                    7775,
                    6548316,
                    8468,
                    6549346,
                    10031,
                    6580216,
                    -1
                ]
            }

result: for 3760901 node value I am expecting 5712962, for 21377132011 node I am expecting 3212, for 9000000000000000000 I am expecting 10031.

the nodes are dynamic and I cant hardcode.

As per the pattern of your requirement, for 21377132011 node it should be 3212 right? — Rakesh Govindula, Jul 07 '23 at 04:07
yes @RakeshGovindula. it was a mistake .Any help would be appreciated. — Andy, Jul 07 '23 at 05:50

score 2 · Accepted Answer · answered Jul 07 '23 at 06:39

2

I am able to achieve your requirement with the below approach.

First I have wrapped your JSON in {} these and given it to source of the dataflow. I have selected Source options-> JSON settings->Single document.

This is the data preview of the source:

enter image description here

Then I took select transformation to extract the columns from Ranks object.

In select transformation, use the Rule based mapping(Go to Add mapping -> Rule based mapping). Give the below configurations.

enter image description here

Data preview of the select:

enter image description here

Now, I took derived column to get the (n-2) element from each array columns.

Click on Add-> Add Column pattern and use the below dynamic expression.

($$ ? integer[])[minus(size(($$ ? integer[])),2)]

enter image description here

Result:

enter image description here

answered Jul 07 '23 at 06:39

Rakesh Govindula

5,257
1
2
11

1

Thanks a ton! Just one more step if you can help me. How to always take the 1st node value from the rank object and write to a sink db..e,g if I always need the 1st value i.e 711 (it can change for a new dataset) value. – Andy Jul 07 '23 at 10:16
I think for this you need to check the first value of split(string(Rank object),'[') contains extracted columns names. You need to use this condition in the select transformation. its better if you raise a new question on this requirement with proper details so that I can explain clearly. – Rakesh Govindula Jul 07 '23 at 11:01

Dataflow Derived Columns to parse complex array

1 Answers1