question

VerttaMoody-8776 avatar image
0 Votes"
VerttaMoody-8776 asked VerttaMoody-8776 commented

Data flow How to remove Null values from sink Json

We are using data flow to transform data from CSV to JSON and want to remove null values from an array before writing to a JSON file. All other results that have null values are automatically removed but it seems the null values remain a part of the array. We are pulling three columns into an array, is there a way to remove null values so they are not included in the JSON file.

[case ( length(replace(replace(replace((split(textbox38,':')[2]),'(',''),')',''),'-','')) > 5,
@(Type='Mobile',Number=(trim(replace(replace(replace((split(textbox38,':')[2]),'(',''),')',''),'-',''))))
),

case ( length(replace(replace(replace((split(textbox255,':')[2]),'(',''),')',''),'-','')) > 5,
@(Type='Home',Number=(trim(replace(replace(replace((split(textbox255,':')[2]),'(',''),')',''),'-','')))) ),

case ( length(replace(replace(replace((split(textbox256,':')[2]),'(',''),')',''),'-','')) > 5,
@( Type='Work',Number=trim(replace(replace(replace((split(textbox256,':')[2]),'(',''),')',''),'-',''))) )
]



Data Preview: (this is what is also written to the file, we want the null value removed from the file)

[["Mobile","3334701112"],["Home","4044771234"],null]

azure-data-factory
· 1
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.

@VerttaMoody-8776 did @MarkKromer-MSFT 's response solve your issue? If so, please mark as accepted answer, otherwise let us know how we may better assist.

0 Votes 0 ·

1 Answer

MarkKromer-MSFT avatar image
0 Votes"
MarkKromer-MSFT answered VerttaMoody-8776 commented

filter(myArray, !isNull(#item))

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

Thank you for your answer, I tried your solution it works for one array, but for another array with multiple elements I am getting an empty array like testData [{}]. Is there a way to filter the empty array?

0 Votes 0 ·