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

Bhardwaj, Sorabh 6

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 38,501 Reputation points Microsoft Employee

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

Hi @Anonymous ,

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 38,501 Reputation points Microsoft Employee

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

Hi @Anonymous ,

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.

4 answers

Vaibhav Chaudhari 38,796 Reputation points

2021-07-09T15:22:16.877+00:00

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
Please sign in to rate this answer.
Bhardwaj, Sorabh 6 Reputation points

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.

Bhardwaj, Sorabh 6 Reputation points

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 38,501 Reputation points Microsoft Employee

2021-07-13T17:32:34.583+00:00

Hi @Anonymous ,

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?

Bhardwaj, Sorabh 6 Reputation points

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ShaikMaheer-MSFT 38,501 Reputation points Microsoft Employee

2021-07-16T12:29:56.61+00:00
Hi @Anonymous ,

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
Please sign in to rate this answer.
Bhardwaj, Sorabh 6 Reputation points

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 38,501 Reputation points Microsoft Employee

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

Hi @Anonymous ,

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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
VIJAY TALARI 0 Reputation points

2024-05-02T08:41:10.57+00:00

Hi Maheer,

Im getting empty collections due to data is null from input. Can you please let me know how we can handle nulls collections and arrays.

I have added dervied column and made subcolumns under derived column and taken aggregation ttransformation as next, used derived column as list in aggregation transformation
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Frankowski, Jaroslaw 0 Reputation points

2024-06-13T10:00:32.82+00:00

We also have the same problem. when moving data between two cosmos dbs - ADF skips fields where null occurs which breaks the application because x.Property == null is different Is_Defined(x.Property)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

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

4 answers

Your answer