Another Way to Work with Nested API Responses: Output and Formatting Options in HTTP Connector

When working with APIs that return structured or nested responses, it's not always necessary to build complex normalization rules or use scripting. The HTTP connector in Etlworks includes a set of native configuration options under the Output and Formatting section that provides an easy and effective way to extract and flatten nested data on the fly.

This article focuses on three configuration options:

• Data JSON Path

• Output as CSV

• Denormalize Nested Fields

These options are available in both the Generic HTTP connector and Pre-Configured API connectors.

Data JSON Path

APIs often return metadata alongside actual records. To extract only the relevant part of the response, use the Data JSON Path setting to define the path to the node that contains the array or object you want to work with.

• Use slash notation (e.g., /data/items) instead of dot notation

• Leave empty if the entire response is already a flat array

Example:

For a response like:

{
  "data": {
    "items": [
     { "id": 1 },
     { "id": 2 }
    ]
  }
}

Use /data/items as the path. Etlworks will then only process the array under items.

Tip: This setting is required for pagination to work if your API returns both data and a pagination token at the same level.

Output as CSV

Enable this option if you want the connector to serialize the flattened API response as CSV. This is especially useful if:

• You're writing to file-based destinations (FTP, SFTP, local)

• The destination system expects a CSV format

• You need to preview or log output in a flat, readable structure

Etlworks will take the extracted and optionally denormalized data and format it as comma-separated values (or a custom separator — see below).
Tip: Select CSV as a source format for HTTP connection.

Denormalize Nested Fields

Many API responses contain embedded objects or arrays. When enabled, this option flattens these nested structures into individual fields using underscore-separated keys.

Example:

For this record:

{
  "user": {
     "id": 123,
     "name": "Alice"
  }
}

You get this flattened output:

user_id, user_name
123, Alice

This option is useful when:

• You plan to map the data to a flat destination like a database or spreadsheet

• You want to output to CSV and retain all nested field values

When to Use This vs. Other Techniques

Use these options when:

• You want a fast, config-only approach to flattening nested API data

• The structure is consistent and doesn’t require recursive flattening or normalization

• You're writing to file-based or relational systems that expect flat data

Use format-based or SQL-based techniques when:

• You need to extract multiple arrays

• You require advanced transformation or joins

• You want to reuse the format across flows and in Etlworks Explorer

Related Resources

• Working with Nested Documents and Formats in Etlworks

• HTTP API Connector Documentation

• Work with Paginated APIs Using the HTTP Connector