Export JSON string but exclude key-value pairs with null/empty values

Question

I'm attempting to output some data originally in csv format from ADF as a JSON string but I need to exclude any key-value pairs where the value is null or empty (e.g. "Report": []).

First I am using an Aggregate transformation to convert 3 complex columns to arrays using the collect() function:

Then I am using a Derived Column transformation to create a masterJSON column using the expression "@(Order=Order, Study=Study, Report=Report)":

Finally I am using a Stringify transformation to convert the masterJSON column into a string which is then sent to a sink:

I've tried many approaches but I cannot get the final JSON string to be output without empty key-value pairs:

The AI answer included seems very close but when I try to use it I get this:

And when I try a different syntax I get this:

I think I'm very close. Can anyone advise?

Answer 1

@David Lang Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

**Ask:**I'm attempting to output some data originally in csv format from ADF as a JSON string but I need to exclude any key-value pairs where the value is null or empty (e.g. "Report": []).

First I am using an Aggregate transformation to convert 3 complex columns to arrays using the collect() function:

Then I am using a Derived Column transformation to create a masterJSON column using the expression "@(Order=Order, Study=Study, Report=Report)":

Finally I am using a Stringify transformation to convert the masterJSON column into a string which is then sent to a sink:

I've tried many approaches but I cannot get the final JSON string to be output without empty key-value pairs:

The AI answer included seems very close but when I try to use it I get this:

And when I try a different syntax I get this:

I think I'm very close. Can anyone advise?

**Solution:**I have come up with a workaround for my issue. At the end we are creating a JSON string so I am simply using the replace() function to remove the characters from the string that I don't want.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

To exclude key-value pairs with null or empty values from the JSON string output, you can use the filter() function in the Derived Column transformation to remove any empty arrays from the "Report" field before creating the masterJSON column. Here's an example expression that should work:

@(Order=Order, Study=Study, Report=Report.filter(item -> item != []))

This expression filters out any empty arrays from the "Report" field before creating the masterJSON column. Then, when you use the Stringify transformation to convert the masterJSON column into a string, it should exclude any empty key-value pairs.

---

References:

• JSON format in Azure Data Factory and Azure Synapse Analytics