Actiknow
Data Engineering

Semi-Structured Data in Redshift: When SUPER and Native Array Functions Simplify Analytics

Learn when Amazon Redshift SUPER and native array functions simplify JSON analytics, when to flatten data, and how to govern cost, quality, and BI reporting.

Data engineers working at computer screens in a modern office on data analytics and integration

A warehouse does not become easier to operate simply because every JSON field has been flattened into a table. For event streams, product attributes, application telemetry, and evolving API payloads, aggressive normalization can produce dozens of staging models, brittle joins, and repeated transformations. Yet leaving everything inside JSON creates a different problem: business definitions become difficult to test, dashboards struggle with unpredictable types, and analysts repeatedly interpret the same payload.

The useful question is not whether semi-structured data is good or bad. It is which fields deserve a stable relational contract, which can remain flexible, and which array operations should be handled directly in SQL. Amazon Redshift’s SUPER data type and its newer native array functions make that decision more practical. They do not remove the need for modeling, ownership, or performance testing.

Contents hide

What changed in Redshift in 2026?

On March 6, 2026, AWS announced nine additional array functions for SUPER: ARRAY_CONTAINS, ARRAY_DISTINCT, ARRAY_EXCEPT, ARRAY_INTERSECTION, ARRAY_POSITION, ARRAY_POSITIONS, ARRAY_SORT, ARRAY_UNION, and ARRAYS_OVERLAP. These functions cover membership checks, deduplication, set comparisons, position lookup, and sorting. AWS says the functions are available in Redshift Regions where the service operates. This is a SQL expressiveness improvement, not a claim that every nested-data query will become faster.

For the precise release scope, consult the AWS announcement of the nine Redshift array functions. For function signatures and related operations, the Redshift array-function reference is the authoritative source. Teams should still validate the engine version, privileges, semantics, and representative workloads in their own environment.

What SUPER actually solves

SUPER stores semi-structured values such as objects and arrays in a Redshift column. Redshift supports SQL-compatible PartiQL navigation, so a query can access attributes with dot notation and array elements with brackets. A nested payload can coexist with conventional columns such as event_id, customer_id, occurred_at, source_system, and ingestion_batch_id.

Imagine an event record with a stable event identifier and timestamp, plus a payload containing device details, a list of campaign tags, and optional attributes introduced by newer application versions. If each optional attribute becomes a separate warehouse column immediately, the schema changes frequently. If the whole record remains opaque, finance and marketing teams cannot reliably reconcile key measures. A hybrid design keeps the identifiers and governed metrics relational while retaining genuinely variable attributes in SUPER.

The important distinction is between a storage convenience and an analytical contract. SUPER is a useful representation for changing structures. It is not, by itself, a semantic layer that defines revenue, active users, orders, or customer eligibility.

Business analytics team reviewing data visualizations and reporting dashboards in an office

An illustrative hybrid event model

A practical event table might contain event_id VARCHAR, occurred_at TIMESTAMP, account_id VARCHAR, event_type VARCHAR, payload SUPER, schema_version VARCHAR, and ingested_at TIMESTAMP. These are illustrative design fields, not a prescribed AWS schema. Store source identifiers and processing metadata outside the payload when they are needed for joins, deduplication, retention, and reconciliation. Preserve the original event contract or raw copy where auditability requires it.

Example payload: {“device”:{“platform”:”ios”},”tags”:[“trial”,”mobile”,”trial”],”features”:[“search”,”recommendations”],”experiment”:{“variant”:”B”}}. A query can retrieve payload.device.platform without first creating a platform dimension. The array functions can check whether tags include mobile, remove duplicate tags, or compare feature sets across events.

The nine array functions, in business terms

ARRAY_CONTAINS answers whether an array includes a value. Use it for event flags, product labels, entitlement tags, or feature exposure. Define how NULL should behave before using the result in a KPI filter.

ARRAY_POSITION returns the first matching element’s zero-based index; ARRAY_POSITIONS identifies all matching positions. These matter when order is meaningful, such as a sequence of funnel steps or ranked recommendations. A missing match is not the same as the first position.

ARRAY_DISTINCT removes repeated values. ARRAY_SORT orders array elements. These can help normalize tag collections for comparison, but do not assume deduplication preserves original order: AWS explicitly states that ARRAY_DISTINCT output order is not guaranteed.

ARRAY_INTERSECTION finds values shared by two arrays; ARRAY_EXCEPT identifies values present in one but not the other; ARRAY_UNION combines unique values. ARRAYS_OVERLAP tests whether two collections have anything in common. These are useful for comparing customer interests with offer categories, old and new entitlements, or feature sets across software releases.

One subtlety deserves special attention: ARRAY_INTERSECTION defaults to multiset behavior, meaning duplicates can be preserved according to their frequencies. Passing TRUE as its distinct argument requests set-style output. ARRAY_UNION, by contrast, returns unique elements. If your business definition is ‘number of shared categories,’ decide whether duplicates represent meaningful repeated occurrences or accidental repetition before writing the metric.

Practical SQL examples that are safe to start with

The following statements illustrate documented Redshift functions using small synthetic arrays. They are not production benchmarks, and teams should test syntax and data types against their deployed Redshift environment.

1. Check whether an event includes a campaign tag

SELECT ARRAY_CONTAINS(JSON_PARSE(‘[“mobile”,”trial”,”email”]’), ‘trial’) AS has_trial_tag;

A true result can support a filter, but it does not automatically establish that a person was enrolled in a trial. The event producer must define what the tag means, when it is set, and whether it can be removed.

2. Deduplicate labels without multiplying a fact table

SELECT ARRAY_DISTINCT(JSON_PARSE(‘[“paid”,”mobile”,”paid”,”new”]’)) AS unique_labels;

This keeps the collection nested. It avoids a join merely to remove repeated tags. If reporting needs a count by tag across customers, however, an exploded tag bridge or a curated aggregate may still be the clearer design.

3. Compare two feature sets

SELECT ARRAY_INTERSECTION(ARRAY(‘search’,’alerts’,’exports’), ARRAY(‘alerts’,’exports’,’billing’), TRUE) AS shared_features;

The third argument requests distinct results. This can support an entitlement comparison or a release audit. It should not replace an authoritative permissions table for access enforcement.

4. Compare tag sets without treating order as meaning

SELECT ARRAY_UNION(ARRAY(‘retail’,’priority’), ARRAY(‘priority’,’online’)) AS combined_tags;

AWS documents that ARRAY_UNION returns unique values and does not guarantee ordering. If a downstream process compares arrays as strings, canonicalize the representation deliberately rather than assuming identical sets always serialize identically.

5. When to unnest instead

SELECT e.event_id, tag FROM event_log AS e, e.payload.tags AS tag;

This is an illustrative PartiQL iteration pattern, assuming event_log exists and payload.tags is an array. It emits one row per tag per event. That row multiplication is exactly what you want for some analyses and exactly what can break an order count or revenue sum if the query is joined back to a fact table without controlling grain. Redshift also supports UNNEST syntax; choose the form that is readable and testable for your team.

The architecture decision: keep nested, flatten, or use both?

Keep a field nested when flexibility has real value

SUPER is attractive for optional attributes, sparse properties, changing event schemas, experimental metadata, and collections that are usually inspected as a whole. A team exploring which app features appeared in a session may be able to use membership and set operations directly without building a permanent table for every possible feature.

A nested representation also reduces pressure to redesign a warehouse schema each time an API adds a noncritical property. This does not excuse ignoring breaking source changes. Track schema versions and validate expected object and array types, especially for fields that influence decisions.

Business and analytics professionals discussing data insights and reporting strategy in a modern office

Flatten when a field becomes a business contract

Promote fields into typed columns or curated tables when they are used repeatedly in critical joins, filters, access controls, published KPIs, or downstream BI models. Revenue amounts, transaction dates, order status, and customer identifiers are typical candidates. A stable contract enables clear typing, null rules, reconciliation, ownership, and predictable query patterns.

A field’s presence in SUPER does not guarantee consistent meaning. The same attribute might change from an integer to a string, from one currency to another, or from a scalar to an array. Expose such a field to executive reporting only after normalizing its business meaning and documenting how malformed or missing values are handled.

Use a hybrid design for most enterprise reporting

A robust pattern is raw event or API payload in a landing layer; a typed, validated staging layer for important fields; and a curated fact-and-dimension or semantic model for business reporting. Keep the residual flexible payload available for exploration and exception diagnosis. This gives analysts access to detail without forcing every dashboard to parse nested data repeatedly.

Actiknow’s business intelligence services cover data integration, modeling, dashboards, and ongoing refresh processes. Those disciplines are relevant because the central decision is not a Redshift function choice in isolation: it is where the organization wants its data contracts and business logic to live.

Five business scenarios where native arrays help

Product analytics and feature adoption

An application emits a list of features used during each session. ARRAY_CONTAINS can identify sessions with a feature; ARRAYS_OVERLAP can identify sessions touching any feature in a defined group. The executive measure still needs a denominator: active sessions, unique users, eligible accounts, or paid customers are different populations.

Retail merchandising and product attributes

A product has variable attributes such as materials, styles, sizes, and labels. Keeping rare or category-specific properties nested can reduce wide, mostly empty tables. Frequently used merchandising dimensions should still be standardized to avoid comparing ‘navy’, ‘dark blue’, and ‘blue’ as if they were separate commercial categories.

Customer segmentation and campaign targeting

Customer interest tags can be compared with campaign categories using overlap or intersection functions. But targeting decisions must also account for consent, current eligibility, suppression lists, and data freshness. An overlap between two arrays is a technical match, not proof that a campaign should be sent.

Software entitlements and configuration drift

Comparing expected and observed feature sets can highlight missing or unexpected entitlements. Use the result as a diagnostic signal, not as the system of record for authorization. The operational system must remain authoritative for who can access a feature.

Event processing and operational diagnostics

Event payloads often carry lists of validation warnings, changed fields, or error codes. Native array functions can classify these records without expanding each list. When operations needs trends by individual error code, a curated exploded table may be more useful for repeated aggregations.

The hidden costs: performance, semantics, and governance

1. Query simplicity is not guaranteed query efficiency

A shorter SQL expression can still scan large payloads, perform repeated array work, or return too many rows. Benchmark using representative data volumes and realistic concurrency. Compare execution time, scanned data, compute consumption, and the performance of downstream BI queries. Do not infer cost savings from a shorter query alone.

2. Array operations do not establish record grain

If one event has four tags and you unnest tags, you have four rows. Joining that result to an order fact and summing revenue without deduplicating or pre-aggregating can multiply the amount. Write down the grain of every intermediate model, and add automated tests for distinct event counts and reconciled totals.

3. NULL, missing, and empty are different

A missing payload attribute, a JSON null, an empty array, and an array containing null may need different treatment. Redshift’s ARRAY_CONTAINS supports a null_match argument, with documented default behavior. Define whether unknown should be excluded, treated as false, or surfaced as a data-quality exception. Do not quietly coerce all cases into the same business answer.

4. Set semantics are not always multiset semantics

Duplicate values may be accidental, or they may represent multiple occurrences. A distinct list of features is different from a count of feature activations. Validate ARRAY_INTERSECTION and ARRAY_EXCEPT behavior for duplicate values and nulls before using their outputs in measures. Where ordering is material, preserve an explicit position or timestamp rather than relying on an unordered set.

5. Flexible schemas still need contracts

Track source ownership, schema version, type changes, accepted fields, retention, and sensitivity. Test whether critical paths exist and have expected types. Keep sample payloads in a controlled test suite. Restrict access to personal or confidential attributes even when they are buried in SUPER; a nested field is not inherently protected.

Data engineer monitoring data processing workflows and analytics dashboards across computer screens

A practical decision matrix for data leaders

Choose SUPER-first exploration when payloads evolve frequently, access patterns are not yet stable, and the analysis is exploratory. Choose curated relational models when fields power contractual KPIs, high-volume dashboards, or business-critical joins. Choose a hybrid when the same source serves both evolving analysis and governed reporting. Revisit the choice when query patterns, data volume, or business definitions change.

Ask five questions before approving the design: What business question is being answered? Which fields have stable definitions? How often will the query run? What is the correct grain? Who owns failures when the payload changes? If those answers are vague, flattening everything or keeping everything nested will both create avoidable problems.

A four-week implementation and validation plan

Week 1: inventory and classify

Inventory three to five representative event or API datasets. Identify high-value nested arrays, their producers, average and extreme payload sizes, schema versions, and current transformation complexity. Classify fields as stable contract, flexible exploratory attribute, sensitive attribute, or obsolete field. Select two real business questions rather than starting with a technology demonstration.

Week 2: build two competing query paths

For each question, implement a SUPER-native query and a curated or flattened alternative. Keep business filters, time windows, and aggregation grain identical. Test membership, deduplication, intersection, missing fields, duplicate elements, and null behavior. Store the SQL in version control and have a second reviewer validate the semantics.

Week 3: benchmark and reconcile

Run both paths on representative historical periods and under realistic concurrency. Capture latency, compute cost, row counts, distinct identifiers, rejected payloads, and metric differences. Reconcile totals against the authoritative source. Include a schema-change test and a backfill or replay test. A result is not production-ready merely because it returns without error.

Week 4: publish contracts and operating controls

Document the selected architecture, owners, accepted schema versions, metric definitions, access permissions, test coverage, and monitoring thresholds. Publish stable reporting views or tables for BI consumers, with raw SUPER available only where justified. Establish change review for source payloads and a clear procedure for investigating discrepancies.

Data engineering team collaborating while monitoring analytics and data pipeline dashboards

Production acceptance criteria

Before release, require evidence that: the selected query matches authoritative counts and financial totals; duplicate and null behavior is explicitly tested; every published metric has a defined grain; sensitive attributes are appropriately restricted; schema changes produce visible failures or alerts rather than silent drift; performance meets a stated workload-specific service objective; and the support team can trace a dashboard number back to its source event or curated table.

The performance threshold must come from the organization’s actual usage. A daily management report and a customer-facing operational dashboard have different latency and freshness needs. Set targets based on user experience and operating budget, not generic claims about a platform.

What executives should measure after adoption

Track the share of critical metrics with approved definitions; the number of schema-change incidents; reconciliation exceptions; time to diagnose a failed payload; query cost per recurring report; dashboard freshness; and the count of one-off transformations that were retired. A reduction in staging-table count is useful only if accuracy, performance, and maintainability remain at least as good.

A good outcome is not ‘more SUPER.’ It is a simpler, well-governed path from changing operational data to dependable business decisions. For related architecture choices, Actiknow’s custom solutions practice describes its database, data-lake, and integration services, which can sit alongside a governed BI delivery model.

Business professionals reviewing analytics reports and performance dashboards during an office meeting

Frequently asked questions

1. What is the SUPER data type in Amazon Redshift?

SUPER is Redshift’s data type for semi-structured values, including nested objects and arrays. It lets teams query flexible payloads with PartiQL-style navigation alongside relational columns.

2. Does SUPER eliminate the need for ETL or ELT?

No. It can reduce some flattening and custom SQL, but critical business fields still require validation, typing, reconciliation, security, and semantic definitions.

3. What was new in March 2026?

AWS announced nine array functions covering membership, position, sorting, deduplication, overlap, and set operations for SUPER arrays. Their value is clearer SQL for common array tasks, not a universal performance guarantee.

4. When should we flatten JSON into relational tables?

Flatten when fields drive frequently used joins, contractual KPIs, governed reporting, or repeatable aggregations where typed schemas and stable grain improve reliability.

5. Can Power BI or Tableau report directly on SUPER?

A BI tool can consume SQL results from Redshift, but exposing raw nested structures directly may complicate modeling and filtering. Curated relational views or tables are usually easier for widely shared dashboards.

6. Are ARRAY_DISTINCT and ARRAY_UNION the same?

No. ARRAY_DISTINCT removes duplicates from one array; ARRAY_UNION combines two arrays into a collection of unique values. Neither should be assumed to preserve a business-significant order.

7. How does ARRAY_INTERSECTION handle duplicates?

Its documented default is multiset behavior, preserving duplicates up to the minimum frequency across the two arrays. A TRUE distinct argument requests set semantics.

8. Can array functions replace a customer-segmentation model?

They can simplify tag matching, but a production segmentation model still needs identity resolution, eligibility rules, consent, freshness controls, and a documented audience definition.

9. Are nested queries always cheaper than flattened tables?

No. The answer depends on scan patterns, payload size, transformations, query frequency, and concurrency. Compare both approaches with workload-specific benchmarks.

10. What should an initial pilot deliver?

Two validated query patterns, a decision on which fields remain nested, reconciliation tests, performance and cost results, documented schema expectations, and a governed BI consumption path.

A sensible next step

If your team is spending more time maintaining JSON flattening logic than answering business questions, start with one event or API dataset and compare a SUPER-native approach against your current reporting model. Actiknow can help assess the data architecture, integration requirements, and reporting implications through its business intelligence and custom solutions services. To discuss a specific dataset or reporting challenge, contact Actiknow.

Sources and further reading

AWS: Amazon Redshift introduces nine new array functions (March 6, 2026).

AWS documentation: Array functions for Amazon Redshift.

AWS documentation: Querying semi-structured data in Amazon Redshift.

AWS documentation: ARRAY_INTERSECTION behavior and distinct argument.