Materialize Logo

map Data Type

New in v0.5.3.

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.

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)

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

Custom types

You can create custom map types, which lets you create a named entry in your Materialize nodes’ catalogs 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:

To map

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
Did this info help?
Yes No