question

SorabhBhardwaj-0162 avatar image
0 Votes"
SorabhBhardwaj-0162 asked ShaikMaheer-MSFT commented

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

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 "".

113374-nulljson-elmenents.png


azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SorabhBhardwaj-0162 ,

Just checking in to see if the below answer provided by @VaibhavChaudhari 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.

0 Votes 0 ·

Hi @SorabhBhardwaj-0162 ,

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.

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered SorabhBhardwaj-0162 commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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..

0 Votes 0 ·

Hi @SorabhBhardwaj-0162 ,

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?

0 Votes 0 ·

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

0 Votes 0 ·
ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @SorabhBhardwaj-0162 ,

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.
115427-image.png

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.
115378-image.png

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.



image.png (82.5 KiB)
image.png (204.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

Hi @SorabhBhardwaj-0162 ,

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.

0 Votes 0 ·