Translating JSON data

Question

I am using Azure Dataflow Mapping to convert/extract data from the JSON file of a similar format given below. Here I have columnNames array that clearly gives me the table column names and the rows a two dimensional holding the values of each row. My target is obvious and that is to combine these two into a proper table so that I can sink the final table into some tabular database.

Need your guidance on how I can use the mapping dataflow expression builder to achieve that. Please also suggest if there is any other suggestion you have.

{
    "items": [
        {
            "tableName": "users",
            "count": 10,
            "columnNames": [
                "id",
                "Name",
                "createdTime",
                "updatedTime",
                "country",
                "displayName",
                "displayOrder",
                "emailNotification",
                "login",
                "data2",
                "notificationPending",
                "passwordExpirationTime",
                "profile",
                "signature",
                "staffGroup"
            ],
            "rows": [
                [
                    "1",
                    "Administrator",
                    null,
                    null,
                    "1",
                    "Administrator",
                    null,
                    null,
                    "administrator",
                    null,
                    "0",
                    "2020-12-30T15:00:00.000Z",
                    "1",
                    null,
                    null
                ],
                [
                    "4",
                    "Someone",
                    null,
                    null,
                    "1",
                    "Someone",
                    "2",
                    null,
                    "Someone",
                    null,
                    "0",
                    null,
                    "15",
                    null,
                    "100134"
                ]
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "link"
        },
        {
            "rel": "canonical",
            "href": "link"
        },
        {
            "rel": "describedby",
            "href": "link",
            "mediaType": "application/schema+json"
        }
    ]
}

Answer 1

Hi Muntasir Joarder ,

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

I understand that you want to convert the multiple array data into tabular format using mapping dataflow.

Similar requirement has been covered in this video: How to Copy Complex multi-array JSON into tabular format using mapping dataflow

Kindly go through the above video and try to implement the approach described in the video. Please let us know if you face any kind of difficulty during the implementation.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thank you.