Working with Nested Documents and Formats in Etlworks

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
JSON	Widely used in APIs and event streams. Native support for nested objects and arrays.
XML	Common in enterprise data exchange. Supports deeply nested elements and attributes.
Avro	Binary format with built-in schema support. Used in streaming and Hadoop ecosystems.
Parquet	Columnar binary format optimized for nested data and analytics workloads.
HL7 v2.x	Delimiter-based healthcare format with structured repeating segments.
HL7 FHIR	Modern JSON/XML-based healthcare format with deeply nested resources.
EDI (X12, EDIFACT, NCPDP, VDA)	Industry-standard formats for structured, often hierarchical business documents.
CLOB	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
Nested Mapping	Map nested fields directly to nested targets using drag-and-drop UI.	Nested ➡ Nested	Transforming complex structures without scripting
Mapping Nested ➡ Flat	Flatten nested objects and arrays into flat rows and columns.	Nested ➡ Flat	Loading into SQL, CSV, or tabular formats
SQL Over Nested Dataset	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 Dataset	Normalize nested arrays into separate flat tables.	Nested ➡ Flat	Relational normalization
Generate Nested JSON Using SQL and CLOB	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
Create Nested Docs with JavaScript	Use scripting to dynamically build nested output.	Flat ➡ Nested	API payloads, JSON/XML document generation
Extract Dimensions	Flatten nested arrays while preserving parent context.	Nested ➡ Flat	Use with JSON/XML formats with repeating elements
Transform Nested XML with XSLT	Use XSLT stylesheets for complex XML-to-XML transformations.	Nested ➡ Nested	Document transformation pipelines
Flatten Repeating XML Segments	Automatically flatten repeating XML elements into flat rows.	Nested ➡ Flat	XML feeds, reports
Flatten Using HTTP Connector Settings	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

Articles in this section

Overview

Supported Nested Formats

Techniques for Working with Nested Data

Comments

Articles in this section

Overview

Supported Nested Formats

Techniques for Working with Nested Data

Related articles