Working with JSON in Redshift: SUPER, PartiQL and Shredding Patterns

Almost every Redshift warehouse we are called into has at least one table full of JSON: event payloads from Kinesis or MSK, API responses landed in S3, jsonb columns replicated in by a zero-ETL integration, or CDC records from DynamoDB. The old answer was JSON_EXTRACT_PATH_TEXT over a VARCHAR(65535) column, which is slow, fragile and silently truncates. The current answer is the SUPER data type queried with PartiQL.

This tutorial covers the parts that actually decide whether a semi-structured design performs: how to get JSON into SUPER, how to navigate and unnest it, and when to stop and shred it into typed columns instead.

1. The SUPER type in one minute

SUPER is a schemaless column type that holds a whole JSON value — scalar, object or array — in Redshift's own binary representation. Key properties to keep in mind:

• A single SUPER value is capped at roughly 1 MB, so a giant payload still belongs in S3 with a pointer in the table.
• SUPER is schemaless: two rows in the same column can have completely different shapes, and a missing path returns null rather than erroring.
• Values keep dynamic types. x.price may be a number in one row and a string in another, which is why comparisons often need an explicit cast.

CREATE TABLE raw.events (
    event_id     BIGINT,
    received_at  TIMESTAMP,
    payload      SUPER
)
DISTSTYLE EVEN
SORTKEY (received_at);

2. Getting JSON in

From S3 with COPY

For newline-delimited JSON where the whole document should land in one SUPER column:

COPY raw.events (payload)
FROM 's3://acme-lake/events/2026/02/'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftLoad'
FORMAT JSON 'noshred'
GZIP;

FORMAT JSON 'noshred' keeps the document intact. The alternative, SERIALIZETOJSON with Parquet, preserves nested Parquet structures into SUPER:

COPY raw.events
FROM 's3://acme-lake/events-parquet/'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftLoad'
FORMAT PARQUET SERIALIZETOJSON;

From a string column already in the warehouse

-- Strict: raises an error on malformed JSON
SELECT JSON_PARSE(raw_text) AS payload FROM staging.landing;

-- Lenient: bad rows become null instead of failing the statement
SELECT JSON_PARSE(raw_text, IGNORE_ERROR => TRUE) AS payload FROM staging.landing;

The lenient form is what you want in a nightly pipeline that must not fail because one producer emitted a truncated line. Count the nulls and alert on them rather than crashing the load.

From streaming ingestion

Streaming materialized views land the Kinesis or MSK record as VARBYTE; the usual pattern is JSON_PARSE(FROM_VARBYTE(kinesis_data, 'utf-8')) inside the materialized view definition, giving you a SUPER column to query downstream.

3. Navigating with PartiQL

PartiQL adds dot and bracket navigation to ordinary SQL:

SELECT
    e.payload.user.id           AS user_id,
    e.payload."order".total     AS order_total,   -- quote reserved words
    e.payload.items[0].sku      AS first_sku
FROM raw.events e;

Three rules save a lot of debugging time:

1. Object navigation is case-sensitive by default. payload.userId and payload.userid are different paths unless you set SET enable_case_sensitive_identifier TO TRUE — and if you do, quote identifiers consistently everywhere.
2. Results are still SUPER. e.payload.user.id returns a SUPER value, not a BIGINT. Joining or grouping on it works, but comparisons with typed columns are safer with an explicit cast: e.payload.user.id::BIGINT.
3. Missing paths yield null, not an error. Good for ragged data, dangerous for typos. Validate new paths with a quick SELECT COUNT(*) WHERE path IS NOT NULL before you build on them.

Useful helpers when the shape is not guaranteed:

SELECT
    JSON_TYPEOF(e.payload.items)              AS items_type,   -- 'array', 'object', 'null'...
    IS_ARRAY(e.payload.items)                 AS is_arr,
    JSON_SERIALIZE(e.payload)                 AS payload_text  -- back to JSON text
FROM raw.events e
LIMIT 10;

JSON_SERIALIZE fails above the varchar limit; JSON_SERIALIZE_TO_VARBYTE handles the large cases.

4. Unnesting arrays

Arrays are flattened with a FROM clause over the array, which behaves like a lateral join:

SELECT
    e.event_id,
    i.sku::VARCHAR       AS sku,
    i.qty::INT           AS qty,
    i.price::DECIMAL(12,2) AS price
FROM raw.events e,
     e.payload.items AS i;

Rows whose items array is empty or missing disappear — this is an inner-join semantic. Keep them with an outer unnest:

FROM raw.events e LEFT JOIN e.payload.items AS i ON TRUE

Need the position of each element (for example to preserve line order):

SELECT e.event_id, idx, i.sku::VARCHAR
FROM raw.events e, e.payload.items AS i AT idx;

Nested arrays chain naturally:

SELECT e.event_id, i.sku::VARCHAR, t::VARCHAR AS tag
FROM raw.events e,
     e.payload.items AS i,
     i.tags AS t;

And an object can be iterated as key/value pairs when producers invent their own attribute names:

SELECT e.event_id, attr_name, attr_value::VARCHAR
FROM raw.events e, UNPIVOT e.payload.attributes AS attr_value AT attr_name;

5. SUPER or shred? The decision that matters

Keeping everything in SUPER is convenient and schema-change-proof. It is also the reason a lot of dashboards are slow, because a SUPER column is a poor citizen for the two things Redshift is fastest at: zone-map pruning on sort keys and hash distribution on join keys.

Our rule of thumb after a fair number of these engagements:

Signal	Do this
Attribute appears in WHERE on most queries	Shred to a typed column and consider it for the sort key
Attribute is a join key	Shred and set as DISTKEY where it helps
Attribute is stable across all producers	Shred; the schema is not really dynamic
Attribute is rare, exploratory or per-tenant custom	Leave in SUPER
Payload needed verbatim for audit or replay	Keep the SUPER column too

The usual production shape is a hybrid: a curated table with the ten or twenty attributes everyone queries as real columns, plus the original SUPER payload alongside for the long tail.

CREATE TABLE analytics.events_curated
DISTKEY (user_id)
SORTKEY (event_ts)
AS
SELECT
    e.event_id,
    e.payload.user.id::BIGINT                       AS user_id,
    e.payload.event_type::VARCHAR(64)               AS event_type,
    TIMESTAMP 'epoch' + e.payload.ts::BIGINT / 1000 * INTERVAL '1 second' AS event_ts,
    e.payload."order".total::DECIMAL(12,2)          AS order_total,
    e.payload                                       AS payload  -- long tail kept
FROM raw.events e;

An incrementally refreshed materialized view over the raw table is the low-maintenance version of the same idea when the raw table is append-only.

6. Performance notes people learn the hard way

• Cast before you filter, and filter on shredded columns where you can. WHERE payload.event_type::VARCHAR = 'purchase' cannot use a zone map; WHERE event_type = 'purchase' on a sort key can skip most blocks.
• Predicates over SUPER do get pushed down into the scan, so filtering early in a subquery is still much better than unnesting first and filtering later.
• Unnesting multiplies rows before aggregation. Aggregate inside a subquery, then join, when a query unnests two arrays from the same row.
• Watch the 1 MB value limit on wide payloads; a row that exceeds it fails the load rather than truncating silently.
• ANALYZE still matters. The planner uses statistics on the SUPER column's row count and size; a freshly loaded raw table with stale stats produces bad join orders.
• Compression is automatic — SUPER uses its own encoding, so do not try to force ZSTD or similar on it.
• Check what the planner actually did with EXPLAIN; a SUPER scan that shows no filter pushdown is a hint that a cast is happening too late.

7. Schema drift, in practice

Semi-structured data drifts. A lightweight monitor over the raw table catches new or vanished attributes before someone's dashboard quietly goes to zero:

SELECT attr_name, COUNT(*) AS rows_with_attr, MIN(received_at), MAX(received_at)
FROM raw.events e, UNPIVOT e.payload AS v AT attr_name
WHERE e.received_at > DATEADD(day, -7, GETDATE())
GROUP BY attr_name
ORDER BY rows_with_attr DESC;

Run it weekly, diff it against last week's result, and route the differences to the team that owns the producer.

Where this usually goes wrong

The two failure modes we are called in to fix are opposite ends of the same spectrum: warehouses that shredded every attribute into a 400-column table and now break on every producer change, and warehouses that left everything in SUPER and now scan the full history for a single event type. The hybrid model above avoids both, but it needs someone to decide which attributes are stable enough to promote — a modelling decision, not a syntax one.

If you are landing JSON in Redshift and the dashboards on top of it have started to drag, our data modeling and architecture and performance optimization engagements cover exactly this work. Get in touch with the table definition and a slow query and we will tell you which half of the problem you have.