Overview
Nested documents are common in modern data exchange formats. They typically include hierarchical or repeating structures such as objects within objects, arrays, or grouped segments. Etlworks supports a wide range of nested data formats and provides multiple techniques for extracting, transforming, flattening, and assembling nested structures.
This article gives you a high-level overview of both the formats that support nested data in Etlworks and the methods available for working with them.
Supported Nested Formats
Etlworks supports the following data formats that can contain nested documents:
| Format | Description |
|
Widely used in APIs and event streams. Native support for nested objects and arrays. |
|
|
Common in enterprise data exchange. Supports deeply nested elements and attributes. |
|
|
Binary format with built-in schema support. Used in streaming and Hadoop ecosystems. |
|
|
Columnar binary format optimized for nested data and analytics workloads. |
|
|
Delimiter-based healthcare format with structured repeating segments. |
|
|
Modern JSON/XML-based healthcare format with deeply nested resources. |
|
|
Industry-standard formats for structured, often hierarchical business documents. |
|
|
The entire content of the nested document (such as JSON or XML) is stored as-is in a single text column without parsing or transformation |
Techniques for Working with Nested Data
Etlworks provides multiple techniques to transform nested data structures into flat datasets or other nested formats.
| Technique | Description | Direction | Best for |
|
Map nested fields directly to nested targets using drag-and-drop UI. |
Nested ➡ Nested |
Transforming complex structures without scripting |
|
|
Flatten nested objects and arrays into flat rows and columns. |
Nested ➡ Flat |
Loading into SQL, CSV, or tabular formats |
|
|
Use SQL to query nested JSON/Avro/Parquet using dot notation. |
Nested ➡ Flat |
Selective field extraction |
|
| Transform Documents Using Source Queries or Formats |
Use Source Query (JavaScript, SQL, or XSLT) or format-level settings (JSON path, XSLT, preprocessor) to reshape the source document before processing. |
Both directions |
Full-document reshaping, passthrough flows, or quick JSON/XML tuning |
|
Normalize nested arrays into separate flat tables. |
Nested ➡ Flat |
Relational normalization |
|
|
Extract data from flat sources into SQLite, use SQL to generate nested JSON, and serialize it using the CLOB format |
Flat ➡ Nested |
Full control over complex JSON document generation from structured sources |
|
|
Use scripting to dynamically build nested output. |
Flat ➡ Nested |
API payloads, JSON/XML document generation |
|
|
Flatten nested arrays while preserving parent context. |
Nested ➡ Flat |
Use with JSON/XML formats with repeating elements |
|
|
Use XSLT stylesheets for complex XML-to-XML transformations. |
Nested ➡ Nested |
Document transformation pipelines |
|
|
Automatically flatten repeating XML elements into flat rows. |
Nested ➡ Flat |
XML feeds, reports |
|
|
Use HTTP connector options (Data JSON Path, Denormalize Nested Fields, Output as CSV) to extract and flatten nested API responses without scripting. |
Nested ➡ Flat |
Quick flattening for file export, API ingestion, or CSV/reporting flows |
|
| Rapid conversion of X12 to XML, JSON or CSV |
Flattening complex, nested X12 messages |
Nested ➡ Nested Nested ➡ Flat |
Easy ingestion of X12 messages into databases, data warehouses, or analytics platforms |