Mapping Data Flow - Null value issue in JSON which removes the complete column from json

Question

Mapping Data Flow - Null value issue in JSON which removes the complete column from json

Sorabh Bhardwaj 1

Azure data factory dataflow not present null values in JSON format
I am looking for how to present my null values with the Azure Data Factory Dataflow activity. Currently, I am using standard dataflow for modelling my data from Azure SQL database and drag it into Blob storage in JSON format. But if the dataset source has a null value, the column in the row is skipped in JSON.

ZdKMo.png

Please, give me some advice on how to proceed, in order to resolve the problem. With Copy activity I don't have the same issue - null values are presented with "".

ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-12T06:45:11.26+00:00

Hi @Sorabh Bhardwaj ,

Just checking in to see if the below answer provided by anonymous userChaudhari helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-16T06:00:36.23+00:00

Hi @Sorabh Bhardwaj ,

Could you please help on sharing details on clarifications which I mentioned in reply of answer. That will help me to repro scenario and provide detailed resolution. Thank you.

ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-12T06:45:11.26+00:00

Hi @Sorabh Bhardwaj ,

Just checking in to see if the below answer provided by anonymous userChaudhari helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-16T06:00:36.23+00:00

Hi @Sorabh Bhardwaj ,

Could you please help on sharing details on clarifications which I mentioned in reply of answer. That will help me to repro scenario and provide detailed resolution. Thank you.

Answer 1

Vaibhav Chaudhari 37,886

Try using Derived Column activity to add "" if the data is null as explained in below. Looks like the question is copied from below as is.

https://stackoverflow.com/questions/61478714/azure-data-factory-dataflow-not-present-null-values-in-json-format

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Sorabh Bhardwaj 1 Reputation point

2021-07-12T15:07:21.89+00:00

I agree that I copied the content as the problem looks similar to my case also derived column doesn't add any value here..I have already a derived column before sink activity where I am declaring the hiereahy..
Please share anything which is validated and verified by you.
Sorabh Bhardwaj 1 Reputation point

2021-07-12T15:09:57.437+00:00

I want Null in json and not in string format like "null"..This is working in copy activity however in dataflows the null json elements itself is missing in output and it is only showing elements if converted to null string value..
ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-13T17:32:34.583+00:00
Hi @Sorabh Bhardwaj ,

Thank you for ask on Microsoft Q&A. Could you please try to help me understand ask better. So that i can repro scenario and share detailed resolution. Thank you.

Are you trying to load data from Azure SQL table to blob storage as json file?

When you load data to json file on blob from Azure SQL table then you want null column values also should come along their key in json file?
Sorabh Bhardwaj 1 Reputation point

2021-07-16T11:42:08.883+00:00

Hi, I am not doing it from Azure SQL table..I have only blob storage where CSV are placed which needs to be converted into another folder in JSON hierarchical structure..I am facing issue when converting null elements in JSON output as if CSV has null value then that element will be eleminated from the output json.

I am able to do this from copy activity but facing issue in Dataflows as need to make hierarchical structure in json.I can only convert null into string and then show those objects and array of objects but showing null value is not seems to be possible in Dataflows.

Any suggestions

Answer 2

ShaikMaheer-MSFT 30,041 Microsoft Employee

Hi @Sorabh Bhardwaj ,

Thank you for posting in Microsoft Q&A Platform.

When you say you csv has null values in it, in which form it has it there? Is it the same way as below.

From csv all the values will be read as strings in Data flows, So We may need to use derived column to pass some other value in to your json for the values null from csv.

In below screenshot I am passing empty string When I see null from csv.

Unfortunately if you try to save null values in to json from data flows its going to skip those keys from json object.

I would encourage you to log feedback item for this. Product team will closely monitor all feedbacks and try to implement them in future releases.

Hope this will help. Thank you.

-------------------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is [how].]4
Want a reminder to come back and check responses? Here is how to subscribe to a [notification].]4

Sorabh Bhardwaj 1 Reputation point

2021-07-16T15:18:57.667+00:00

Thanks for your work out on this. However I need null value in json and I know it skipp those elements where value is set as null..I need to show output in json in null only and not string null or not in blank string quotes.

This is possible if we use copy activity where in we can have null in json for null value in CSV but seems it's not possible in Dataflows..

Anyways thanks for trying.
ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2021-07-16T16:49:02.07+00:00

Hi @Sorabh Bhardwaj ,

yes you are right. In Copy activity its possible to have. But in data flows its not possible directly. We can either go with empty string or string null and then look a work around based on requirement.

I would encourage you to log feedback item for this. Product team will closely monitor all feedbacks and try to implement them in future releases.

Please Accept above Answer. Accepting answer will help community. Thank you.

Mapping Data Flow - Null value issue in JSON which removes the complete column from json

2 answers

Activity