Search code examples
azure-data-lake-gen2azure-data-factory

Azure Data Factory data flow not reading JSON object due to null value

I'm working on an Azure Data Factory (ADF) data flow that reads JSON data from a file. However, I'm encountering an issue where the data flow is failing to read JSON objects due to a null value.

Here's a sample JSON data with the null value:

[
    null,
    {
        "Column2": "KM120",
        "Mon": 8,
        "Tue": 8,
        "Wed": 8,
        "Thu": 8,
        "Fri": 8
    },
    {
        "Column2": "KM121",
        "Mon": 8,
        "Tue": 8,
        "Wed": 8,
        "Thu": 8,
        "Fri": 8
    }
]

The error message I'm getting is:

Error occurred when deserializing source JSON file. Check if the data is in valid JSON object format. Error reading JObject from JsonReader. Current JsonReader item is not an object: Null. Path '[0]', line 2, position 5.

I have checked the JSON file and it is in the correct format. I suspect the issue is with the null value in the first element of the array.

Can anyone suggest how to resolve this issue and make the data flow read the JSON objects even with null values?

Thanks in advance.

I removed the null value from the JSON file and tested the data flow, which resolved the issue. However, I'm looking for a way to automate this process since manually editing each file is time-consuming. I'm exploring the option of using a custom JSON schema in the data flow that can handle null values, but I haven't been able to get it to work so far. I'm still investigating this solution and open to other suggestions.

Solution

  • Can anyone suggest how to resolve this issue and make the data flow read the JSON objects even with null values?

    If your Json file is in Azure Blob Storage or ADLS gen 2 you can access that file using the rest API ang read the Json object with Null values.

    To achieve this follow below procedure:

    • First Go to your storage account and change access level of container to container then copy the file path of your Json file. 31-1 1
    • Then create a linked service as rest api and access this file from there paste the copied url from blob storage as base url and select authentication as Anonymous.enter image description here
    • Then create data flow and select rest api linked service as source and import the schema. 31-1 2
    • take select transformation and do mapping ass below: enter image description here

    Output:

    enter image description here

    Note: It is possible only if your file is in Azure Blob Storage or ADLS gen 2