Cloud

map

The map data type stores a collection of key-value pairs with a uniform key type and a uniform value type. Because PostgreSQL has no map type, Redpanda SQL represents a map on the wire as an array of (key, value) pairs, in both the text and binary formats.

Keys and values

A map’s key type and value type are fixed for the whole map. Keys are never nullable: the key type is always a required type. Values can be nullable or required, depending on the source schema or how you build the map.

Syntax

Build a map value with the map function, passing alternating keys and values:

map(key1, value1 [, key2, value2 ... ])

Values can be literals, composite ROW(…) values, or NULL.

Create a map

SELECT map('a', 1, 'b', 2);

Redpanda SQL displays a map as an array of (key, value) pairs, with each pair shown as a quoted composite value:

       map
-------------------
 {"(a,1)","(b,2)"}
(1 row)

A value can be NULL. For a pair with a null value, Redpanda SQL shows nothing after the comma:

SELECT map('a', 1, 'b', NULL);

       map
------------------
 {"(a,1)","(b,)"}
(1 row)

A value can be a composite ROW(…), which Redpanda SQL displays as a quoted, nested composite:

SELECT map('a', ROW(1, 2));

        map
-------------------
 {"(a,\"(1,2)\")"}
(1 row)

Because a map is displayed like an array of pairs, use pg_typeof to confirm that a value is a map:

SELECT pg_typeof(map('a', 1));

     pg_typeof
-------------------
 map(text,integer)
(1 row)

Access map values

Retrieve a value by its key with the subscript operator. When you subscript an inline map() constructor, wrap the constructor in parentheses so the subscript applies to the map it returns:

SELECT (map('a', 1, 'b', 2))['a'];

This returns the value 1.

Subscript a map column directly, without parentheses. See Query map columns from a topic.

If the key is not present in the map, the subscript returns NULL:

SELECT (map('a', 1))['z'];

This returns NULL.

A NULL lookup key is also treated as a key miss and returns NULL, even though a map’s keys are never null:

SELECT (map('a', 1))[NULL];

This returns NULL.

Looking up a key always returns a nullable result, because a lookup that doesn’t match a key returns NULL. If the value type is nullable, the result has the same type. If the value type is required, the result is the nullable form of that type.

Duplicate keys

A map can contain duplicate keys. Redpanda SQL preserves every pair you provide, in order, and does not deduplicate or compact them. This keeps a map loaded from Iceberg identical to the one stored.

When you look up a key that appears more than once, the last matching pair wins:

SELECT (map('a', 1, 'a', 2))['a'];

This returns 2.

Because the last pair wins, a later pair that sets the key to NULL shadows any earlier value for that key. A key whose winning value is NULL is indistinguishable from a missing key on lookup, because both return NULL. The two differ only when you display the whole map, which still shows every stored pair, including the ones with null values.

Duplicate keys can also come from a source schema. Redpanda SQL applies the same last-pair-wins rule to those keys.

Query map columns from a topic

When a topic’s registered schema includes a map field (Avro or Protobuf), Redpanda SQL maps it to a map column when you create the table. Because a map is a composite type, the table’s struct_mapping_policy controls this mapping: the default COMPOUND policy produces a map column, while the JSON policy stores the field as JSON, as it does for any composite type. The map’s keys and values take the SQL types that correspond to the source key and value types. For an Iceberg-enabled topic, Redpanda SQL reads map columns from both the topic’s live records and its Iceberg-committed history.

Access values in a map column with the subscript operator:

SELECT attributes['color'] FROM products;

Limitations

Maps have the following restrictions, which apply independently.

Building a map with map()
- A map key must be a supported scalar type, such as a text, integer, floating-point, boolean, timestamp, date, UUID, or byte-string type. Composite types (row), arrays, maps, and decimals can’t be used as keys.
- A map value can’t be a decimal type.
- map() accepts only literal keys and values. Values can be ROW(…) literals. Column references and other non-literal expressions aren’t supported.
- map() requires an even number of arguments, alternating keys and values.
Subscripting with []
- The key you subscript a map with must be a supported scalar type. A map whose key type is a composite (row) can be loaded from a source schema but can’t be subscripted.
- Slicing a map, such as ['a':'b'], is not supported.
Using a map in a query
- A map can’t be used in GROUP BY, ORDER BY, or DISTINCT, because the type has no equality or ordering operator.
- A map can’t be cast to text.
Loading a map from Iceberg
- Map columns loaded from an Iceberg-enabled topic have no map-specific restrictions. You can load struct, array, and map keys and values, including maps nested inside structs.
- The only unsupported shapes are those that the array rules forbid: a list whose element is another list fails with Multi-dimensional arrays are not supported, and a list whose element is a map fails with Arrays of maps are not supported. Both errors occur at CREATE TABLE or REFRESH.

What do you think of this page?

Let us know more:

Let us contact you about your feedback:

map