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.

Answer 1

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:

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.

Data preview of the select:

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)]

Result: