CODEWITHSUNDEEP
azure-data-factoryderived-column
sssithhh
sssithhh

Reputation: 13

Set complex type to null in ADF Data flow and omit it

Sink seems to omit automatically all primitive types, if they are null, I need the same for a complex type objects, when all their attributes are null, because currently I end up with state "complexType": {} , so I need to omit that empty object somehow.

In a Derived Column I tried using an expression like:

iif(!isNull(complexType.field) && !isNull(complexType.field1) && !isNull(complexType.field2),
complexType, null)

but that doesn't seem to work.

Upvotes: 1

Views: 769

Answers (2)

Paul Marshall
Paul Marshall

Reputation: 643

I had a very similar situation and manged to find the solution in part due to this post. My case: I had to have a derived column output a newly created complex object (in my case an extended json date field) but where the field was null (or as the OP phrased it "omitted" - meaning this field was simply not in the output json) in certain circumstances. The error I was receiving was the same as yours when I used an IIF conditional statement. The solution: Use a "case" statement instead, but provide only a "true_expression".

A bit more detail: if you use a "case" with the following syntax:

case(condition,true_expression,false_expression)

you get quite similar behaviour to if you use an "iif". However, you can also use a case statement with the following syntax:

case(condition,true_expression)

then this provides an output equal to the "true_expression" when the condition is matched, but outputs null if it is not met. In my case the expression was as follows for a derived column called DatePrinted:

case(!isNull(DatePrintedString), @({$date}=@({$numberLong}=DatePrintedEpoch)))

The outcome here is that if "DatePrintedString" is not null, the output json contains a property called "DatePrinted" with the required complex object, but if "DatePrintedString" is null, the output json simply does not have a property called "DatePrinted" (which I believe is the behaviour the OP described as desirable).

In your case you seem to be trying to set an existing field to null. In my case I am creating a new derived field. To make this work for your case, you may need to have a placeholder property (such as complexTypeInput) on your Source data to hold the incoming source object, and then output a newly minted derived column called complexType in your output mapping.

Your resulting code would likely look something like this: (Assuming an input property called complexTypeInput and a derived column name of complexType

case(!isNull(complexTypeInput.field) || !isNull(complexTypeInput.field1) || !isNull(complexTypeInput.field2), complexTypeInput)

Effectively: If any of the fields is not null, return the complexTypeInput, otherwise do nothing (so the derived property would not appear on the output json object).

Upvotes: 0

Aswin
Aswin

Reputation: 7156

Expression should return the same type 'null' as previous expressions.

The above error occurs in ADF dataflow, when expressions in iif are of different types. If one expression is of complex type and other is of null type, it is not possible to update the value. Also, you cannot convert the null value to complex datatype. Only way to replace {} empty object is to convert the expressions in iif to string type. Below is the correct expression.

iif(isNull(complexfield.field1)&&isNull(complexfield.field2),toString(null()),toString(complexfield))

This expression checks if the field1 and field2 fields of the complexfield object are null, and if they are, it returns a null value using toString(null()). If the fields are not null, it returns the complexfield object as a string using toString(complexfield).

Upvotes: 0

Related Questions