map type

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

Syntax

Map functions + operators

Operators

Functions

map_length(m: mapany) -> int

map_build(kvs: list record(text, T)) -> map[text=>T]

map_agg(keys: text, values: T) -> map[text=>T]

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