Convert relational DB data to JSON file using data flow

Kvankud 0

I have data in a relational database that I want to convert to JSON format. The relational data and the required JSON hierarchy are shown in the images below:

User's image

Can someone please help me with this as soon as possible? Thank you in advance.

Kvankud 0 Reputation points

2024-08-06T22:38:20.7+00:00

@KranthiPakala-MSFT , Can you assist on this?

1 answer

AnnuKumari-MSFT 32,906 Reputation points Microsoft Employee

2024-08-07T05:35:00.92+00:00

Hi Kvankud ,

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

As I understand you want to convert relational data into nested JSON format by performing data transformation using ADF.

You can make use of Derived column transformation and aggregate transformation in ADF to achieve this requirement.

Derived column transformation provides us the option to create columns and subcolumns which when loaded to json file would come as nested json .

To combine set of jsons into an array , purses[] in your case, you can make use of collect() function in aggregate transformation.

For details regarding the implementation, kindly watch out the following video from the suggested timestamp: How to convert relational data into JSON using Dataflow.

Hope it helps. Kindly accept the answer if it helped. Thankyou
Please sign in to rate this answer.
Kvankud 0 Reputation points

2024-08-07T05:42:47.52+00:00

Hello @AnnuKumari-MSFT I was able to solve the issue, however, I'm unable to get a clean JSON file.
During sink process data preview, it gives an error as "undefined" is not valid JSON. Post that I converted the JSON column toString(column_name) and now I get escape characters in the resulting JSON as :
"date":"2024-01-01T02:02:18.000Z","createdAt":"2024-01-01T02:03:21.000Z"
I do not want extra "" that are added.
Please help on this.

AnnuKumari-MSFT 32,906 Reputation points Microsoft Employee

2024-08-07T06:52:43.4166667+00:00

Kvankud ,

Kindly ensure that your data does not have undefined or null values that could cause issues . You can replace it by empty string using this expression: iifNull(columnname,'') or using column pattern : iifNull($$,'')

About the double quotes that is coming, it is expected to be generated for keys and values in the json, but in case you want to get rid of it, You can try a workaround by using the following expression : replace(jsonColumn, '\"', '')

Kvankud 0 Reputation points

2024-08-07T15:44:23.56+00:00

Hello @AnnuKumari-MSFT ,
I tried that but did not work.
The thing is in my complete required JSON file there are 20+ columns and that are needed to be under a parent column as sub-column.
And when I try and map that in derived column, Data preview throws above "undefined" error.

And to counter that I did the below step in Modify column activity.

this previews the data properly, even after downloading the preview CSV file.
However after I map this data flow to a data flow activity in ADF, it then sinks the JSON with a backslash() before every double quote (")
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Convert relational DB data to JSON file using data flow

1 answer

Your answer