# Query Topics with Nested Fields

> For the complete documentation index, see [llms.txt](https://docs.redpanda.com/llms.txt). Component-specific: [cloud-data-platform-full.txt](https://docs.redpanda.com/cloud-data-platform-full.txt)

---
title: Query Topics with Nested Fields
latest-operator-version: v26.1.4
latest-console-tag: v3.7.3
latest-connect-version: 4.93.0
latest-redpanda-tag: v26.1.9
docname: query-data/query-nested-fields
page-component-name: cloud-data-platform
page-version: master
page-component-version: master
page-component-title: Cloud
page-relative-src-path: query-data/query-nested-fields.adoc
page-edit-url: https://github.com/redpanda-data/cloud-docs/edit/main/modules/sql/pages/query-data/query-nested-fields.adoc
description: Map a topic's nested fields to typed SQL columns and query them by name.
page-topic-type: how-to
personas: app_developer, data_engineer
learning-objective-1: Map a topic with a nested schema as a SQL table using struct_mapping_policy = 'COMPOUND'
learning-objective-2: Query nested fields using ROW field-access syntax
learning-objective-3: Resolve cyclic-reference errors
page-git-created-date: "2026-05-26"
page-git-modified-date: "2026-05-26"
---

<!-- Source: https://docs.redpanda.com/cloud-data-platform/sql/query-data/query-nested-fields.md -->

When a [topic](https://docs.redpanda.com/cloud-data-platform/reference/glossary/#topic)'s schema includes nested Protobuf, Avro, or JSON message types, you can map those nested structures as user-defined types (UDTs) with named fields. UDT columns are queryable using SQL `row` field-access syntax instead of opaque JSON, so nested fields are queryable by name, includable in projections, and usable in `WHERE`, `GROUP BY`, and `ORDER BY` clauses without parsing JSON at query time.

After reading this page, you will be able to:

-   Map a topic with a nested schema as a SQL table using struct\_mapping\_policy = 'COMPOUND'

-   Query nested fields using ROW field-access syntax

-   Resolve cyclic-reference errors


## [](#prerequisites)Prerequisites

Before you query a topic with nested fields:

-   [Enable Redpanda SQL](https://docs.redpanda.com/cloud-data-platform/sql/get-started/deploy-sql-cluster/) on your Redpanda Bring Your Own Cloud (BYOC) cluster.

-   [Connect to Redpanda SQL](https://docs.redpanda.com/cloud-data-platform/sql/connect-to-sql/) with `psql` or another PostgreSQL client.

-   Register a schema for the topic in schema-registry, including one or more nested message types.

-   The topic’s data is reachable through a Redpanda catalog. The `default_redpanda_catalog` is created and linked for you when Redpanda SQL is enabled.


## [](#map-the-topic-as-a-sql-table)Map the topic as a SQL table

Create the SQL table with `struct_mapping_policy = 'COMPOUND'` to surface each nested message as a user-defined type column:

```sql
CREATE TABLE default_redpanda_catalog=>orders WITH (
  topic = 'orders',                            (1)
  schema_subject = 'orders-value',             (2)
  struct_mapping_policy = 'COMPOUND'           (3)
);
```

| 1 | Required. The Redpanda topic to map. |
| --- | --- |
| 2 | Optional. The Schema Registry subject. Defaults to <topic>-value when omitted. |
| 3 | Optional. Defaults to 'COMPOUND', which surfaces nested structures as user-defined types. |

Replace `orders` with your topic name. Your topic must have a schema registered in Schema Registry. For details on the `schema_subject` option, see [CREATE TABLE](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-statements/create-table/).

For a topic schema with this Protobuf definition:

```proto
message Order {
  string order_id = 1;
  Customer customer = 2;
  double amount = 3;
}

message Customer {
  string customer_id = 1;
  string name = 2;
  string region = 3;
}
```

Redpanda SQL maps the table with three columns: `order_id` (text), `customer` (a user-defined type with fields `customer_id`, `name`, and `region`), and `amount` (double precision).

> 💡 **TIP**
>
> `COMPOUND` is the default `struct_mapping_policy`. To map nested structures as opaque JSON instead, use `struct_mapping_policy = 'JSON'`. Cyclic types require `struct_mapping_policy = 'JSON'`. See [Handle recursive (cyclic) schemas](#handle-recursive-cyclic-schemas).

## [](#query-nested-fields)Query nested fields

Access a nested field by its declared name using the `(column).field` form. Wrap the column in parentheses:

```sql
SELECT order_id, (customer).name, (customer).region, amount
FROM default_redpanda_catalog=>orders
WHERE (customer).region = 'EMEA';
```

To project every field of a nested structure as separate result columns, use the wildcard `.*` form:

```sql
SELECT order_id, (customer).*
FROM default_redpanda_catalog=>orders
LIMIT 10;
```

For schemas with multiple levels of nesting, chain the parenthesized field access. For example, if `Customer` itself contained a nested `address` message with a `zip_code` field, you would query the zip code as:

```sql
SELECT ((customer).address).zip_code FROM default_redpanda_catalog=>orders;
```

For the full `row` reference, including comparison operators, NULL handling, and `::text` casting, see [`row`](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-data-types/row/).

## [](#handle-recursive-cyclic-schemas)Handle recursive (cyclic) schemas

The `COMPOUND` policy does not support recursive (cyclic) schemas, such as a `Comment` message that references itself or two messages that reference each other. Trying to map such a schema with `COMPOUND` fails at table-creation time with the following error:

```text
Cyclic reference at '<parent>.<field>' → '<type>'. Cyclic types are not supported in COMPOUND struct mapping policy; use struct_mapping_policy=JSON for recursive types.
```

Re-create the table with `struct_mapping_policy = 'JSON'`. In JSON mode, Redpanda SQL stores each nested structure as a JSON value:

```sql
CREATE TABLE default_redpanda_catalog=>comments WITH (
  topic = 'comments',
  struct_mapping_policy = 'JSON'
);
```

Query JSON-mapped fields with standard JSON functions instead of `row` field access. See [`json`](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-data-types/json/).

## [](#choose-between-compound-and-json)Choose between COMPOUND and JSON

| Policy | Use when | Trade-offs |
| --- | --- | --- |
| COMPOUND (default) | The topic schema has nested structures that are not recursive, and you want to query nested fields directly by name. | Typed access; usable in WHERE, GROUP BY, ORDER BY. Required if you query an Iceberg-enabled topic via a linked Redpanda catalog, so that nested fields stay typed across both live records and the topic’s Iceberg history. |
| JSON | The topic schema is recursive, or you prefer flexible access through JSON functions. | Recursive types supported; fields are untyped until extracted with JSON functions. Queries that span the Redpanda topic and its linked Iceberg table do not align cleanly, because Iceberg always exposes nested structures as typed columns. |

## [](#suggested-reading)Suggested reading

-   [Query streaming topics](https://docs.redpanda.com/cloud-data-platform/sql/query-data/query-streaming-topics/): Query a topic without Iceberg history.

-   [Query Iceberg-enabled topics](https://docs.redpanda.com/cloud-data-platform/sql/query-data/query-iceberg-topics/): Query a topic with both its live streaming data and Iceberg history. Use `struct_mapping_policy = 'COMPOUND'` so nested fields align between the Redpanda topic and the linked Iceberg table.

-   [`row`](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-data-types/row/): Full reference for the `row` data type, including comparisons, NULL semantics, and conversion to text.

-   [CREATE TABLE](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-statements/create-table/): Complete option list for mapping a Redpanda topic to a SQL table.