Handling arrays, nested JSON, and complex fields

Summary

Clockspring processors expect flat fields. Arrays or nested objects require extraction or flattening before most processors can handle them.

Symptom

• Errors about unsupported field types

• Database writes fail

• RecordReader cannot parse nested fields

• JSONPath expressions return null or blank values

Root Cause

• Nested objects are not supported directly in SQL

• Arrays require splitting or flattening

• RecordReaders interpret complex structures differently

• EvaluateJsonPath may extract only top-level fields

How to Fix It

Flatten structures

Use JoltTransformJSON or UpdateRecord to convert nested fields into flat key/value pairs.

Extract array elements

Use SplitJson or EvaluateJsonPath with wildcards to break arrays into multiple FlowFiles.

Store raw JSON if needed

If the structure is too complex, store the full JSON in a text or jsonb column.

Validate RecordReader schema

Ensure schema matches the flattened structure.

Prevention

• Avoid deep nesting when building integrations

• Document expected field layout

• Test data transformations before database writes