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

Bhardwaj, Sorabh 1 Reputation point
2021-07-09T14:36:18.787+00:00

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
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,726 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Vaibhav Chaudhari 38,646 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


  2. ShaikMaheer-MSFT 38,126 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.
    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.

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

    • 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

  3. 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. User's image

    I have added dervied column and made subcolumns under derived column and taken aggregation ttransformation as next, used derived column as list in aggregation transformationUser's image

    0 comments No comments