map type

map data expresses an unordered map with text keys and an arbitrary uniform value type.

Detail Info
Quick Syntax '{a=>123.4, b=>111.1}'::map[text=>double]'
Size Variable
Catalog name Anonymous, but nameable

Syntax

' map_string ' :: MAP [ TEXT => value_type ]
Field Use
map_string A well-formed map object.
value_type The type of the map’s values.

Map functions + operators

Operators

Operator RHS Type Description
-> string Access field by name, and return target field (docs)
@> map Does element contain RHS? (docs)
<@ map Does RHS contain element? (docs)
? string Is RHS a top-level key? (docs)
?& string[] Does LHS contain all RHS top-level keys? (docs)
?| string[] Does LHS contain any RHS top-level keys? (docs)

Functions

Function Computes
map_length(m: mapany) -> int
Return the number of elements in m.
map_build(kvs: list record(text, T)) -> map[text=>T]
Builds a map from a list of records whose fields are two elements, the first of which is text. In the face of duplicate keys, map_build retains value from the record in the latest positition. This function is purpose-built to process Kafka headers.
map_agg(keys: text, values: T) -> map[text=>T]
Aggregate keys and values (including nulls) as a map. (docs)

Details

Construction

A well-formed map is a collection of key => value mappings separated by commas. Each individual map must be correctly contained by a set of curly braces ({}).

You can construct maps from strings using the following syntax:

SELECT '{a=>123.4, b=>111.1}'::map[text=>double] as m;
  m
------------------
 {a=>123.4,b=>111.1}

You can create nested maps the same way:

SELECT '{a=>{b=>{c=>d}}}'::map[text=>map[text=>map[text=>text]]] as nested_map;
  nested_map
------------------
 {a=>{b=>{c=>d}}}

Constraints

  • Keys must be of type text.
  • Values can be of any type as long as the type is uniform.
  • Keys must be unique. If duplicate keys are present in a map, only one of the (key, value) pairs will be retained. There is no guarantee which will be retained.

Custom types

You can create custom map types, which lets you create a named entry in the catalog for a specific type of map.

Currently, custom types only provides a shorthand for referring to otherwise-annoying-to-type names, but in the future will provide binary encoding and decoding for these types, as well.

Valid casts

Between maps

Two map types can only be cast to and from one another if they are structurally equivalent, e.g. one is a custom map type and the other is a built-in map and their key-value types are structurally equivalent.

From map

You can cast map to and from the following types:

  • text (by assignment)
  • Other maps as noted above.

To map

  • text (explicitly)
  • Other maps as noted above.

Examples

Operators

Retrieve value with key (->)

Retrieves and returns the target value or NULL.

SELECT '{a=>1, b=>2}'::map[text=>int] -> 'a' as field_map;
 field_map
-----------
 1
SELECT '{a=>1, b=>2}'::map[text=>int] -> 'c' as field_map;
 field_map
----------
 NULL

Field accessors can also be chained together.

SELECT '{a=>{b=>1}}, {c=>{d=>2}}'::map[text=>map[text=>int]] -> 'a' -> 'b' as field_map;
 field_map
-------------
 1

Note that all returned values are of the map’s value type.


LHS contains RHS (@>)

SELECT '{a=>1, b=>2}'::map[text=>int] @>
       '{a=>1}'::map[text=>int] AS lhs_contains_rhs;
 lhs_contains_rhs
------------------
 t

RHS contains LHS (<@)

SELECT '{a=>1, b=>2}'::map[text=>int] <@
       '{a=>1}'::map[text=>int] as rhs_contains_lhs;
 rhs_contains_lhs
------------------
 f

Search top-level keys (?)

SELECT '{a=>1.9, b=>2.0}'::map[text=>double] ? 'a' AS search_for_key;
 search_for_key
----------------
 t
SELECT '{a=>{aa=>1.9}}, {b=>{bb=>2.0}}'::map[text=>map[text=>double]]
        ? 'aa' AS search_for_key;
 search_for_key
----------------
 f

Search for all top-level keys (?&)

Returns true if all keys provided on the RHS are present in the top-level of the map, false otherwise.

SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY['b', 'a'] as search_for_all_keys;
 search_for_all_keys
---------------------
 t
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY['c', 'b'] as search_for_all_keys;
 search_for_all_keys
---------------------
 f

Search for any top-level keys (?|)

Returns true if any keys provided on the RHS are present in the top-level of the map, false otherwise.

SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY['c', 'b'] as search_for_any_keys;
 search_for_any_keys
---------------------
 t
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY['c', 'd', '1'] as search_for_any_keys;
 search_for_any_keys
---------------------
 f

Count entries in map (map_length)

Returns the number of entries in the map.

SELECT map_length('{a=>1, b=>2}'::map[text=>int]) as count;
 count
---------------------
 2
Back to top ↑