Transforming Avro Data to JSON Array Format in Apache NiFi

Learn how to convert SQL results into a nested JSON array format using Apache NiFi with Jolt and practical examples.
---
This video is based on the question https://stackoverflow.com/q/75438388/ asked by the user 'Albert' ( https://stackoverflow.com/u/4837948/ ) and on the answer https://stackoverflow.com/a/75439341/ provided by the user 'Barbaros Özhan' ( https://stackoverflow.com/u/5841306/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: NiFi convert avro to JSON array format with nested array

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Transforming Avro Data to JSON Array Format in Apache NiFi

Apache NiFi is a powerful tool for managing data flows, but sometimes you might face challenges when converting data from one format to another. A common task is transforming SQL results, especially when working with Avro data, into a well-structured JSON format. In this guide, we will explore how to convert SQL results into a nested JSON array format using Apache NiFi, specifically by leveraging the JoltTransformJSON processor.

The Challenge: Transforming SQL Results to a Nested JSON Format

Let's take a look at the SQL result we are dealing with. For example, assume we have the following SQL results:

member_idfield2totaltaxshippartnumpriceqty87445096324017173.950001593596642187445096324017173.950000075600932187445096324017173.950001217929399.951While using processes like ConvertRecord or ConvertAvroToJSON, you might get a flattened result:

[[See Video to Reveal this Text or Code Snippet]]

However, your requirement is to group these results and represent the partnum, price, and quantity as nested arrays. The desired output format is:

[[See Video to Reveal this Text or Code Snippet]]

The Solution: Using JoltTransformJSON Processor

To achieve the desired transformation, we can use the JoltTransformJSON processor in NiFi. Here's a step-by-step guide on how to do it.

Step 1: Shift Operation

The first operation we'll use is shift. It allows us to rearrange and format the data as needed:

[[See Video to Reveal this Text or Code Snippet]]

This operation takes each item and restructures it into nested arrays under itemDetails while preserving the other attributes.

Step 2: Cardinality Operation

Next, we apply a cardinality operation to ensure that we group multiple records into a single key.

[[See Video to Reveal this Text or Code Snippet]]

This specifies that for the repeating itemDetails, we want to collect as many entries as there are, ensuring they are grouped together.

Step 3: Sort Operation

After restructuring our data, we can use the sort operation to order our attributes in a sensible manner.

[[See Video to Reveal this Text or Code Snippet]]

Additional Considerations: Handling New Cases

If your data contains different sets grouped by unique identifiers such as FIELD2 or MEMBER_ID, you can adjust your Jolt spec accordingly. Here’s a more advanced approach to accommodate that scenario:

Shift the data, designating the unique attribute as part of the output.

Cardinality to handle repeated fields as before.

Shift again to eliminate redundant keys and organize the output.

Example Advanced Spec

Here's how an extended Jolt spec might look when handling different groupings:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Transforming data formats in Apache NiFi doesn’t have to be overwhelming. With the JoltTransformJSON processor, you can efficiently convert SQL results into a structured JSON array format, accommodating complex nested structures. By following the steps outlined above, you can customize your data transformation to meet your specific requirements.

Implementing these transformations can significantly streamline your data processes, making your data flows more organized and meaningful. Enjoy utilizing NiFi to its fullest potential!

Видео Transforming Avro Data to JSON Array Format in Apache NiFi канала vlogize