Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,639 questions
filter(myArray, !isNull(#item))
Data flow How to remove Null values from sink Json
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 47%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVM%3C/text%3E%3C/svg%3E)
Vertta Moody
1
Reputation point
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
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
MartinJaffer-MSFT 26,236 Reputation points2021-10-20T17:30:17.813+00:00 @Vertta Moody did @MarkKromer-MSFT 's response solve your issue? If so, please mark as accepted answer, otherwise let us know how we may better assist.
Sign in to comment
1 answer
Sort by: Most helpful
-
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator2021-10-18T22:35:10.527+00:00 -
Vertta Moody 1 Reputation point
2021-10-27T02:43:45.453+00:00 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?
Sign in to comment -