Querying Paimon Semi-Structured Data with StarRocks: Variant, Shredding & SQL Practices

Introduction: Semi-Structured Data in Lakehouse Analytics

In scenarios like e-commerce orders, user behavior, and device logs, each record typically contains two types of information: stable business fields (order_id, event_time, amount) suited for explicit schema management, and continuously changing extension fields (product attributes, marketing info, client parameters) often stored as JSON Payloads. When such JSON data enters a lakehouse and is frequently queried, repeatedly parsing full JSON text, looking up paths, judging types, and converting at runtime becomes costly as dynamic fields and access frequency grow.

Variant offers an alternative: it stores semi-structured data as a typed binary structure, preserving schema flexibility while reducing repeated JSON parsing costs. If a few internal paths become long-term high-frequency hotspots, Parquet Shredding can further save those paths as typed sub-columns, enabling better columnar reads and computation. StarRocks reads Parquet Variant via Paimon Catalog and accesses dynamic fields using get_variant_*, variant_query, variant_typeof, and CAST. This article covers the differences between regular Parquet JSON and Variant, how StarRocks reads Paimon Variant, how Shredding optimizes hot paths, and how to choose the right data organization in practice.

Key point: Variant is not meant to replace all formal typed columns. Core fields participating in partitioning, joins, sorting, and strong-SLA queries should remain formal columns; if data is mainly for raw archival with almost no internal querying, regular Parquet JSON may be simpler. The decision hinges on whether internal paths need repeated querying, whether types need fidelity, and whether hotspot fields are worth further columnarization.

1. What Is Variant and How Does It Differ from Regular Parquet JSON?

Regular Parquet JSON: Flexible but Opaque to Parquet

Regular Parquet JSON refers to storing JSON Payload as STRING, BINARY, or Parquet JSON Logical Type — not StarRocks native JSON type. For example, an order event payload:

{
  "sku": "SKU-001",
  "channel": "app",
  "campaign_id": 9001,
  "paid": true,
  "items": [
    {
      "sku": "SKU-001",
      "qty": 2
    }
  ]
}

When saved as regular JSON in Parquet, the underlying layer is typically a single BYTE_ARRAY. Parquet knows the column stores JSON, but internal paths like sku, channel, campaign_id are not independent typed Parquet columns. Querying campaign_id requires: (1) reading full JSON Payload, (2) parsing JSON text, (3) locating target path, (4) converting result to needed data type. If multiple queries repeatedly access the same path, these parsing and conversion costs are paid repeatedly.

Variant: Flexible Structure, No Longer Just Text

Variant is a typed binary representation for semi-structured data. Objects, arrays, and scalars are encoded at write time into: metadata: stores field names, encoding info, and other metadata value: stores value type, position, and actual content

Logically, different rows can still have different fields; physically, data is no longer a JSON text that must be parsed from the start. For example, these structurally different rows — even with same-path type mismatches — can coexist in one Variant column:

{"campaign_id": 9001}
{"campaign_id": "unknown"}
{"coupon": "NEW20"}

Apache Parquet has defined Variant's binary encoding and Shredding layout.

Comparison: Regular Parquet JSON vs Parquet Variant

Variant's core advantages are threefold: (1) retains semi-structured schema flexibility, (2) reduces repeated JSON text parsing cost at query time, (3) via Shredding, gives hotspot paths typed, columnar physical representation. However, Variant does not guarantee smaller files or faster all queries. If data is write-once read-once, or queries always return full Payload, regular JSON may be simpler.

2. How StarRocks Reads Paimon Variant

During StarRocks querying of Paimon Variant, Paimon manages table Snapshots, Schema, and data files; StarRocks reads Parquet files and executes Variant queries. The overall architecture involves three phases:

2.1 Obtain Paimon Table and Split Information

StarRocks gets table structure and current Snapshot via Paimon Catalog; Paimon plans data file Splits. StarRocks extracts Split file info and uses StarRocks Parquet Reader to read Variant.

2.2 Convert Parquet Variant to Columnar Data

Parquet Reader reads based on physical Schema in file:

Plain Variant: metadata and value Shredded Variant: metadata, value, and typed_value Read results are organized as StarRocks Variant column vectors, participating in vectorized filtering, projection, aggregation, etc.

2.3 Access Internal Fields via Variant Functions

Users can use different functions to access Variant: get_variant_string: extract string get_variant_int: extract integer get_variant_double: extract floating-point get_variant_bool: extract boolean variant_query: return Variant at specified path variant_typeof: inspect Variant value type CAST: convert Variant to target SQL type

3. How Shredding Optimizes Variant Reads

Plain Variant's physical structure simplifies to:

payload
├── metadata
└── value

It already avoids repeated JSON syntax parsing, but all internal fields remain inside Variant's binary structure. If business repeatedly accesses few hotspot paths long-term (e.g., $.sku, $.channel, $.campaign_id, $.paid), Shredding can save these paths as typed sub-columns:

payload
├── metadata
├── value
└── typed_value
     ├── sku          STRING
     ├── channel      STRING
     ├── campaign_id  BIGINT
     └── paid         BOOLEAN

Shredding does not turn Variant into a fixed-schema STRUCT. Non-shredded long-tail fields stay in value; type-mismatched data also remains in value, preserving original semantics. Example: campaign_id mostly integer, but one row writes string "unknown". Integer values go to typed_value.campaign_id; conflicting values stay in generic Variant value.

Shredding's read advantages come from: (1) hotspot paths have explicit types, reducing runtime type judgment and conversion; (2) hotspot paths become independent typed Parquet sub-columns, leveraging Parquet encoding and compression; (3) querying hotspot fields directly uses typed_value, reducing generic Variant decoding; (4) typed sub-columns provide physical basis for path-level column pruning and Parquet statistics filtering.

A Paimon table Snapshot references multiple Parquet files. During schema evolution, early files can use Plain Variant layout, later files Shredded Variant layout. Both are Parquet files; difference lies only in Variant column's physical Schema. StarRocks Parquet Reader reads per file's actual Schema and presents consistent Variant semantics to query layer.

4. How to Query Paimon Variant with StarRocks

Using an e-commerce order event table as example, with Alibaba Cloud DLF-managed Paimon Catalog as environment.

4.1 Connect Paimon Catalog in StarRocks

CREATE EXTERNAL CATALOG paimon_dlf
PROPERTIES (
  "type" = "paimon",
  "paimon.catalog.type" = "rest",
  "uri" = "https://cn-hangzhou-vpc.dlf.aliyuncs.com",
  "paimon.catalog.warehouse" = "<dlf_catalog_name>",
  "token.provider" = "dlf"
);

paimon.catalog.warehouse

is DLF Catalog name; uri and dlf.region need actual DLF instance region config. After creation, view table structure: DESC paimon_dlf.demo.order_events; Result shows:

order_id      BIGINT
shop_id       BIGINT
event_time    DATETIME
amount        DECIMAL(18,2)
status        VARCHAR
payload       VARIANT

4.2 View Full Variant

SELECT
  order_id,
  payload,
  variant_typeof(payload) AS payload_type
FROM paimon_dlf.demo.order_events
LIMIT 10;

For sample order data, payload_type is usually Object.

4.3 Extract Fields by Type

SELECT
  order_id,
  get_variant_string(payload, '$.sku') AS sku,
  get_variant_string(payload, '$.channel') AS channel,
  get_variant_int(payload, '$.campaign_id') AS campaign_id,
  get_variant_bool(payload, '$.paid') AS paid
FROM paimon_dlf.demo.order_events;

If path missing or value cannot convert to target type, get_variant_* returns NULL.

4.4 Read Nested Objects and Arrays

SELECT
  order_id,
  variant_query(payload, '$.items[0]') AS first_item,
  get_variant_string(payload, '$.items[0].sku') AS first_item_sku,
  get_variant_int(payload, '$.items[0].qty') AS first_item_qty
FROM paimon_dlf.demo.order_events;

variant_query

returns Variant, suitable for further nested access; for explicit SQL types, use get_variant_* or CAST. Example:

SELECT
  order_id,
  CAST(
    variant_query(payload, '$.campaign_id')
    AS BIGINT
  ) AS campaign_id
FROM paimon_dlf.demo.order_events;

4.5 Filter and Aggregate Using Variant Fields

SELECT
  order_id,
  get_variant_string(payload, '$.sku') AS sku
FROM paimon_dlf.demo.order_events
WHERE get_variant_string(payload, '$.channel') = 'app'
  AND get_variant_bool(payload, '$.paid') = true;

Can also extract dynamic fields first, then aggregate:

WITH extracted AS (
  SELECT
    get_variant_string(payload, '$.channel') AS channel,
    get_variant_bool(payload, '$.paid') AS paid
  FROM paimon_dlf.demo.order_events
)
SELECT
  channel,
  COUNT(*) AS event_count,
  SUM(CASE WHEN paid THEN 1 ELSE 0 END) AS paid_count
FROM extracted
GROUP BY channel;

Thus, dynamic Payload in Paimon tables participates in StarRocks filtering, aggregation, and analysis like ordinary typed columns.

5. Choosing Between Regular JSON, Plain Variant, Shredded Variant, and Formal Typed Columns

In e-commerce order scenario, a more reasonable modeling approach: order_id, shop_id, event_time, amount, status → formal typed columns

Product attributes, promotion rules, payment callbacks, channel extensions → Variant sku, channel, campaign_id (stable hotspot paths) → Shredding

If a dynamic field gradually becomes core filter/join/partition field → promote to formal typed column

Variant's value is not stuffing all fields into one Payload, but providing a better balance between fixed schema and fully dynamic JSON.

6. Frequently Asked Questions

6.1 Is Variant Always Faster Than Regular JSON?

Not necessarily. Variant's advantage comes from avoiding repeated JSON parsing and providing typed access for hotspot paths. If queries always read full Payload, data is read once, or data scale is small, regular JSON may be simpler.

6.2 Does Shredding Turn Variant into Fixed-Schema STRUCT?

No. Shredding only saves selected hotspot paths as typed sub-columns; long-tail fields and type-conflict values remain in generic value, so Variant's dynamic structure persists.

6.3 What Happens When Same Path Has Different Types?

Example: most campaign_id are integers, few rows write string "unknown". Values matching Shredding type enter typed_value; conflicting values stay in generic Variant content. Queries should still handle NULL, type checks, and conversion failures per business semantics.

6.4 Which Fields Should Not Stay Long-Term in Variant?

Fields long participating in partitioning, joins, sorting, primary keys, or strong-SLA filters should be promoted to formal typed columns. Variant suits continuously evolving extension attributes, not hiding entire business schema in one Payload.

7. Conclusion: When Is Paimon Variant Worth Using?

Regular Parquet JSON solves dynamic data storage, but frequent internal field analysis incurs continuous text parsing and type conversion costs. Variant encodes semi-structured data as typed binary structure, preserving schema flexibility while bringing dynamic fields back into columnar computation system.

On this basis, Shredding further saves hotspot paths as typed Parquet sub-columns, reducing generic Variant decoding and type conversion, and providing physical basis for column pruning, encoding compression, and statistics filtering.

Via Paimon DLF REST Catalog, StarRocks users can directly use get_variant_*, variant_query, variant_typeof, and CAST to query Paimon Variant data, placing dynamic Payload and ordinary structured fields in the same SQL analytics pipeline.

Related code merged into StarRocks Main branch, to be released in next version. Future articles will cover usage patterns and applicable scenarios per version progress.

8. References

Apache Parquet Variant Encoding: https://parquet.apache.org/docs/file-format/types/variantencoding/

Apache Parquet Variant Shredding: https://parquet.apache.org/docs/file-format/types/variantshredding/