Running Large Text and JSON Documents in Amazon Redshift SUPER Columns

Applications often receive data that does not fit neatly into a fixed relational schema. API responses, event payloads, application logs, user profiles, configuration documents, and metadata may contain different fields from one record to another.

A common approach is to store this type of data as JSON text in a VARCHAR column. That works for basic storage, but querying nested fields becomes more difficult because the application or database has to repeatedly parse the JSON document.

Amazon Redshift provides the SUPER data type for storing semi-structured data such as JSON documents. With SUPER, developers can keep nested objects and arrays together while still querying individual attributes from SQL.

This article explains how to use SUPER for large JSON and text-based documents, how it differs from storing JSON as plain text, and what to consider before using it in a production Redshift workload.

What Is the SUPER Data Type?

SUPER is a native Amazon Redshift data type designed for semi-structured data.

Instead of flattening every property into a separate relational column, a SUPER column can contain objects, arrays, and nested values.

For example, an application may produce this document:

{
  "user": {
    "id": 1001,
    "name": "Rahul"
  },
  "event": "purchase",
  "amount": 2499,
  "products": [
    {
      "id": 501,
      "name": "Keyboard",
      "quantity": 1
    },
    {
      "id": 502,
      "name": "Mouse",
      "quantity": 2
    }
  ]
}

A traditional relational design could require several tables to represent this structure.

With SUPER, the document can be stored as one semi-structured value.

CREATE TABLE application_events
(
    event_id BIGINT,
    event_data SUPER
);

This is particularly useful when the JSON structure changes frequently or contains optional fields.

Why Use SUPER Instead of VARCHAR for JSON?

The simplest way to store JSON is as text.

For example:

CREATE TABLE application_events
(
    event_id BIGINT,
    event_data VARCHAR(MAX)
);

The database stores the JSON as a string.

The problem starts when applications need to query information inside that string regularly.

With SUPER, the JSON structure is represented as semi-structured data rather than just a block of text.

For example:

INSERT INTO application_events
VALUES
(
    1001,
    JSON_PARSE('{
        "event": "login",
        "user": {
            "id": 501,
            "name": "Amit"
        }
    }')
);

The nested values can then be accessed directly.

SELECT
    event_data.event,
    event_data.user.id,
    event_data.user.name
FROM application_events;

This is much easier to work with than treating the entire JSON document as an opaque string.

Creating a SUPER Column

A table can contain both regular relational columns and SUPER columns.

CREATE TABLE orders
(
    order_id BIGINT,
    customer_id BIGINT,
    order_date DATE,
    order_details SUPER
);

This design is useful when some information is stable while other information changes over time.

For example:

order_id is stable.
customer_id is stable.
order_date is stable.
order_details may contain different fields depending on the order type.

This hybrid design is often more practical than putting the entire record into JSON.

Loading JSON Into SUPER

One common approach is to convert JSON text into SUPER using JSON_PARSE.

INSERT INTO orders
(
    order_id,
    customer_id,
    order_date,
    order_details
)
VALUES
(
    10001,
    501,
    '2026-11-09',
    JSON_PARSE('{
        "status": "shipped",
        "shipping": {
            "method": "express",
            "tracking": "TRK10001"
        },
        "items": [
            {
                "sku": "KB-100",
                "quantity": 1
            }
        ]
    }')
);

The important part is that the JSON document becomes a SUPER value when it is parsed.

For larger ingestion pipelines, JSON files can also be loaded from Amazon S3 using Redshift's data loading capabilities. The exact ingestion design depends on whether the source produces individual JSON records, newline-delimited JSON, or another supported format.

Querying Nested JSON Properties

Once the data is stored as SUPER, nested attributes can be accessed using dot notation.

SELECT
    order_details.status,
    order_details.shipping.method
FROM orders;

You can also filter using nested properties.

SELECT
    order_id,
    order_details.shipping.method
FROM orders
WHERE order_details.status = 'shipped';

This becomes useful when the document contains information that is not needed in every query.

Instead of creating a separate relational column for every possible property, the application can keep less predictable fields inside the SUPER document.

Working With Arrays

JSON documents commonly contain arrays.

For example:

{
  "order_id": 10001,
  "items": [
    {
      "sku": "KB-100",
      "quantity": 1
    },
    {
      "sku": "MS-200",
      "quantity": 2
    }
  ]
}

SUPER supports querying nested arrays using Amazon Redshift's PartiQL capabilities.

A query can iterate through the array:

SELECT
    o.order_id,
    item.sku,
    item.quantity
FROM orders AS o,
     o.order_details.items AS item;

This is useful for reporting situations where one document contains multiple child objects.

For example, a single order row can contain several products while the query still returns individual item records.

Converting SUPER Back to JSON

Sometimes an application or integration needs the complete document as JSON text.

Redshift provides JSON_SERIALIZE for converting a SUPER value into JSON text.

SELECT
    order_id,
    JSON_SERIALIZE(order_details) AS order_json
FROM orders;

This is useful when exporting data to another system or returning a document through an application layer.

However, applications should avoid repeatedly converting SUPER to JSON text and then parsing it again. If the data is already stored as SUPER, query the required attributes directly whenever possible.

SUPER vs VARCHAR for JSON

The choice between SUPER and VARCHAR depends on how the data will be used.

Requirement	SUPER	VARCHAR JSON
Store JSON document	Yes	Yes
Query nested properties	Natural fit	More processing required
Work with arrays	Yes	More complicated
Handle changing fields	Good	Good
Treat JSON as opaque text	Possible	Simple
Repeated analytical queries	Better fit	Less convenient
Application needs original raw text exactly	Not always ideal	Better fit

If the document is primarily an archive that will rarely be queried internally, VARCHAR may still be appropriate.

If developers need to analyze nested fields regularly, SUPER is usually a better starting point.

SUPER vs Normalized Tables

SUPER does not mean that traditional relational modeling is no longer necessary.

Consider customer information such as:

Customer ID
Email
Country
Account status
Registration date

If these fields are stable and frequently used for joins, filters, aggregations, and reporting, regular columns are usually easier to manage.

SUPER becomes more useful for data such as:

{
  "preferences": {
    "language": "en",
    "notifications": {
      "email": true,
      "sms": false
    }
  },
  "devices": [
    {
      "type": "mobile",
      "os": "Android"
    }
  ]
}

These structures may evolve independently from the core relational model.

A practical design can therefore combine both approaches.

Relational columns
    |
    +-- IDs
    +-- Dates
    +-- Status
    +-- Frequently queried business attributes

SUPER column
    |
    +-- Nested metadata
    +-- Optional properties
    +-- API payload details
    +-- Variable application attributes
    +-- Arrays

Handling Large Documents

SUPER is useful for large semi-structured documents, but it should not become a replacement for object storage.

For example, storing application metadata inside SUPER can make sense:

{
  "document_type": "invoice",
  "source": "erp",
  "customer": {
    "id": 501
  },
  "processing": {
    "status": "completed"
  }
}

Storing a large binary file, image, video, or PDF directly inside a SUPER document is a different problem.

For those workloads, Amazon S3 is generally a better storage layer.

A better architecture might look like this:

Redshift
    |
    +-- Document ID
    +-- Customer ID
    +-- Metadata in SUPER
    +-- S3 object location

Amazon S3
    |
    +-- Original PDF
    +-- Image
    +-- Large binary document

This keeps analytical metadata in Redshift while large objects remain in object storage.

Designing the JSON Structure

The flexibility of SUPER can make it tempting to put everything into one deeply nested document.

That usually creates maintenance problems.

Prefer a structure that reflects the application's real data model.

For example:

{
  "customer": {
    "id": 501
  },
  "order": {
    "number": "ORD-10001",
    "status": "shipped"
  },
  "shipping": {
    "method": "express"
  }
}

Avoid unnecessarily deep structures such as:

{
  "data": {
    "payload": {
      "response": {
        "result": {
          "information": {
            "details": {}
          }
        }
      }
    }
  }
}

Deep nesting makes SQL harder to read and makes downstream data processing more difficult.

Performance Considerations

Using SUPER does not automatically make every JSON workload fast.

Query design still matters.

If a query repeatedly reads the same nested property, consider whether that property should become a regular column or be exposed through a materialized analytical structure.

For example, if nearly every dashboard filters on order status, keeping status as a normal relational column may be more efficient and easier to manage:

CREATE TABLE orders
(
    order_id BIGINT,
    customer_id BIGINT,
    status VARCHAR(30),
    order_details SUPER
);

The flexible information remains inside SUPER while frequently accessed attributes are promoted into relational columns.

Also avoid unnecessary serialization:

JSON_SERIALIZE(order_details)

followed by parsing the resulting text again.

Query the SUPER value directly when the database can perform the required operation without converting it to text.

Schema Evolution

One of the strongest reasons to use SUPER is handling changing JSON structures.

Imagine that an application initially produces:

{
  "event": "login",
  "user_id": 1001
}

Later, the application adds:

{
  "event": "login",
  "user_id": 1001,
  "device": {
    "type": "mobile",
    "os": "Android"
  }
}

A relational schema might require a migration to introduce the new columns.

A SUPER-based design can accommodate the additional structure without changing the table definition.

However, schema flexibility does not eliminate the need for governance.

Teams should still define which fields are stable, which are optional, and which are experimental.

Validating JSON Before Ingestion

SUPER provides flexibility, but invalid or unexpected application data can still create operational problems.

Validate important business fields before loading data.

For example, an ingestion service can check:

Required:
    event
    event_id
    timestamp

Optional:
    device
    location
    campaign

The validation layer should also detect unexpected data types.

A property that normally contains:

"customer_id": 501

should not silently become:

"customer_id": "unknown"

without a clear reason.

Dynamic schemas are useful, but inconsistent data types can make analytics much harder.

Common Mistakes

Storing Everything as SUPER

SUPER is flexible, but not every field needs to be semi-structured.

Keep stable, frequently queried business attributes as normal columns where appropriate.

Treating SUPER as Object Storage

Do not use a SUPER column as a replacement for Amazon S3 when the workload involves large binary files or objects.

Store the object externally and keep the relevant metadata and reference in Redshift.

Creating Extremely Deep JSON

Deep nesting makes queries harder to maintain.

Keep document structures understandable and consistent.

Skipping Data Validation

Flexible schemas still need quality checks.

Validate required fields and data types before analytical workloads depend on them.

Returning the Entire Document for Every Query

Avoid selecting large SUPER documents when the application needs only two or three attributes.

Instead of:

SELECT *
FROM orders;

prefer:

SELECT
    order_id,
    order_details.status
FROM orders;

This keeps data processing focused on what the query actually needs.

Using SUPER for Highly Relational Data

If the same fields are joined and aggregated constantly, hiding them inside a JSON document can make the data model unnecessarily complicated.

Use SUPER where its flexibility provides a real benefit.

Production Checklist

Before using SUPER for a production workload, check the following:

Identify which fields should remain relational columns.
Use SUPER for genuinely semi-structured information.
Define a consistent JSON document structure.
Validate required fields before ingestion.
Monitor changes to JSON properties and data types.
Avoid unnecessarily deep nesting.
Keep large binary objects in object storage.
Query required nested attributes instead of serializing entire documents.
Promote frequently queried attributes to relational columns when appropriate.
Test representative document sizes and query patterns before production rollout.
Review access controls when SUPER contains sensitive information.
Document important fields so downstream teams know which properties are supported.

Advantages and Disadvantages

Advantages

Handles nested JSON and semi-structured data.
Reduces the need for immediate schema changes.
Supports objects and arrays.
Works alongside traditional relational columns.
Makes JSON analytics easier than treating documents as plain text.
Fits event, API, metadata, and application payload workloads.

Disadvantages

Flexible schemas can become difficult to govern.
Complex nested queries can be harder to maintain.
Not every workload benefits from semi-structured storage.
Large documents can increase processing requirements.
Frequently queried fields may still be better represented as relational columns.
SUPER should not be treated as a replacement for object storage.

When Should You Use SUPER?

SUPER is a good fit when the data has a meaningful semi-structured shape and that shape is useful for analytical queries.

Good examples include:

Application event payloads
API responses
User metadata
Product attributes
Configuration documents
IoT events
Log metadata
Nested transaction details
Data from systems with evolving schemas

A traditional relational model is usually better when the structure is stable and the same fields are frequently joined, filtered, grouped, and constrained.

Summary

Amazon Redshift SUPER provides a practical way to work with JSON and other semi-structured data without forcing every property into a fixed relational schema.

The key is not to treat SUPER as a replacement for relational database design. Instead, use it where the data is naturally nested, optional, or expected to evolve.

A strong production design often combines both approaches: keep stable business fields in regular Redshift columns and store flexible document details in SUPER.

For large documents, also separate analytical metadata from large binary content. Redshift can store and analyze the metadata while Amazon S3 remains the appropriate location for files and other large objects.

Used this way, SUPER gives developers more flexibility without giving up the advantages of a relational analytical database.