How to check if a value is null in Azure Data Factory

Question

I am trying to use Azure Data Factory to run an API call. This is the data I get from the SQL database before I pass it to the API call:

 {
    "document_send_date": "2023-06-26T00:00:00Z",
    "onboarding_form_send_date": null
}

Sometimes the values look like the first item. Sometimes, the value is null. For this API call, I need to use a date format of 'yyyy-MM-dd'. I am attempting to use formatDateTime() as follows:

@concat('{
    "data": [
        {
            "document_send_date": "', formatDateTime(item().document_send_date, 'yyyy-MM-dd'), '",
            "onboarding_form_send_date": "', formatDateTime(item().onboarding_form_send_date, 'yyyy-MM-dd'), '"
        }
    ]
}')

to convert the datetime values into just a date value. However, when the value is null, the formatDateTime() function returns an error. Is there any way to check if the value being passed into formatDateTime() is null, and if so, return 'null' and if not, return the date string of the datetime passed into the function?

Answer 1

You can use if(condition,exp1,exp2) function to check whether the value is null or not.

Change your expression like below:

@concat('{
    "data": [
        {
            "document_send_date": "', if(not(equals(item().document_send_date,null)),formatDateTime(item().document_send_date, 'yyyy-MM-dd'),item().document_send_date),'",',
            '"onboarding_form_send_date": "', if(not(equals(item().onboarding_form_send_date,null)),formatDateTime(item().onboarding_form_send_date, 'yyyy-MM-dd'),item().onboarding_form_send_date),'"
        }
    ]
}')

• Here, the above expression, checks for null values. If those are not null, it gives date, otherwise it will take null values.
• As we are dealing with strings, the above expression will convert null to empty string in the JSON. If you want, you can change the string as per your requirement in the else case.

Here, to show the output in a JSON format, I have appended the above resulted JSON into an array using append activity inside ForEach and used json() around the above expression.

Result: