376/functions/map.html


<!DOCTYPE html>

<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  <meta name="lang:clipboard.copy" content="Copy to clipboard">
  <meta name="lang:clipboard.copied" content="Copied to clipboard">
  <meta name="lang:search.language" content="en">
  <meta name="lang:search.pipeline.stopwords" content="True">
  <meta name="lang:search.pipeline.trimmer" content="True">
  <meta name="lang:search.result.none" content="No matching documents">
  <meta name="lang:search.result.one" content="1 matching document">
  <meta name="lang:search.result.other" content="# matching documents">
  <meta name="lang:search.tokenizer" content="[\s\-]+">

  
    <link href="https://fonts.gstatic.com/" rel="preconnect" crossorigin>
    <link href="https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700|Roboto:300,400,400i,700&display=fallback" rel="stylesheet">

    <link href="https://fonts.googleapis.com/css2?family=Alata&family=Open+Sans:ital,wght%400,300;0,400;0,600;1,400&display=swap" rel="stylesheet">
    <style>
      body,
      input {
        font-family: "Roboto", "Helvetica Neue", Helvetica, Arial, sans-serif
      }

      code,
      kbd,
      pre {
        font-family: "Roboto Mono", "Courier New", Courier, monospace
      }
    </style>
  

  <link rel="stylesheet" href="../_static/stylesheets/application.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-palette.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-fixes.css"/>
  
  <link rel="stylesheet" href="../_static/fonts/material-icons.css"/>
  
  <meta name="theme-color" content="2196f3">
  <script src="../_static/javascripts/modernizr.js"></script>
  
  
    <title>Map functions and operators &#8212; Trino 376 Documentation</title>
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <link rel="stylesheet" href="../_static/material.css" type="text/css" />
    <link rel="stylesheet" type="text/css" href="../_static/copybutton.css" />
    <link rel="stylesheet" type="text/css" href="../_static/trino.css" />
    <script id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/language_data.js"></script>
    <script src="../_static/clipboard.min.js"></script>
    <script src="../_static/copybutton.js"></script>
    <script async="async" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.7/latest.js?config=TeX-AMS-MML_HTMLorMML"></script>
    <script type="text/x-mathjax-config">MathJax.Hub.Config({"tex2jax": {"inlineMath": [["\\(", "\\)"]], "displayMath": [["\\[", "\\]"]], "processRefs": false, "processEnvironments": false}})</script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Mathematical functions and operators" href="math.html" />
    <link rel="prev" title="Machine learning functions" href="ml.html" />
  
   
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-133457846-1');
      gtag('config', 'AW-1036784065');
      window._linkedin_data_partner_ids = ['2842796'];
    </script>
    <script async src="https://snap.licdn.com/li.lms-analytics/insight.min.js"></script>
  </head>
  <body dir=ltr
        data-md-color-primary= data-md-color-accent=>
  
  <svg class="md-svg">
    <defs data-children-count="0">
      
      <svg xmlns="http://www.w3.org/2000/svg" width="416" height="448" viewBox="0 0 416 448" id="__github"><path fill="currentColor" d="M160 304q0 10-3.125 20.5t-10.75 19T128 352t-18.125-8.5-10.75-19T96 304t3.125-20.5 10.75-19T128 256t18.125 8.5 10.75 19T160 304zm160 0q0 10-3.125 20.5t-10.75 19T288 352t-18.125-8.5-10.75-19T256 304t3.125-20.5 10.75-19T288 256t18.125 8.5 10.75 19T320 304zm40 0q0-30-17.25-51T296 232q-10.25 0-48.75 5.25Q229.5 240 208 240t-39.25-2.75Q130.75 232 120 232q-29.5 0-46.75 21T56 304q0 22 8 38.375t20.25 25.75 30.5 15 35 7.375 37.25 1.75h42q20.5 0 37.25-1.75t35-7.375 30.5-15 20.25-25.75T360 304zm56-44q0 51.75-15.25 82.75-9.5 19.25-26.375 33.25t-35.25 21.5-42.5 11.875-42.875 5.5T212 416q-19.5 0-35.5-.75t-36.875-3.125-38.125-7.5-34.25-12.875T37 371.5t-21.5-28.75Q0 312 0 260q0-59.25 34-99-6.75-20.5-6.75-42.5 0-29 12.75-54.5 27 0 47.5 9.875t47.25 30.875Q171.5 96 212 96q37 0 70 8 26.25-20.5 46.75-30.25T376 64q12.75 25.5 12.75 54.5 0 21.75-6.75 42 34 40 34 99.5z"/></svg>
      
    </defs>
  </svg>
  
  <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer">
  <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search">
  <label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
  <a href="#functions/map" tabindex="1" class="md-skip"> Skip to content </a>
  <div id="announcement">
  <div id="announcement-content">
    Presto SQL is now Trino
    <a href="https://trino.io/blog/2020/12/27/announcing-trino.html" target="_blank">Read why &raquo;</a>
  </div>
</div>
<header class="md-header" data-md-component="header">
  <nav class="md-header-nav md-grid">
    <div class="md-flex navheader">
      <div class="md-flex__cell md-flex__cell--shrink">
        <a href="/" title="Trino"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/trino.svg" height="26"
                   alt="Trino 376 Documentation logo">
          
        </a>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
      </div>
      <div class="md-flex__cell md-flex__cell--stretch">
        <div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
          <span class="md-header-nav__topic">Trino 376 Documentation</span>
          <span class="md-header-nav__topic"> Map functions and operators </span>
        </div>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
        
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" action="../search.html" method="GET" name="search">
      <input type="text" class="md-search__input" name="q" placeholder="Search"
             autocapitalize="off" autocomplete="off" spellcheck="false"
             data-md-component="query" data-md-state="active">
      <label class="md-icon md-search__icon" for="__search"></label>
      <button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">
        &#xE5CD;
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="result">
          <div class="md-search-result__meta">
            Type to start searching
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>

      </div>
      
        <div class="md-flex__cell md-flex__cell--shrink">
          <div class="md-header-nav__source">
            <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
          </div>
        </div>
      
      
  <script src="../_static/javascripts/version_dropdown.js"></script>
  <script>
    var json_loc = "../../versions.json",
        target_loc = "../../",
        text = "Versions";
    $( document ).ready( add_version_dropdown(json_loc, target_loc, text));
  </script>
  

    </div>
  </nav>
</header>

  
  <div class="md-container">
    
    
    <!-- empty -->
    <main class="md-main">
      <div class="md-main__inner md-grid" data-md-component="container">
        
          <div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                <nav class="md-nav md-nav--primary" data-md-level="0">
  <label class="md-nav__title md-nav__title--site" for="__drawer">
    <a href="/" title="Trino" class="md-nav__button md-logo">
      
        <img src="../_static/trino.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Trino 376 Documentation">Trino 376 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
    </div>
  
  
  <ul class="md-nav__list">
    <li class="md-nav__item">
    
    
      <a href="../overview.html" class="md-nav__link">Overview</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../installation.html" class="md-nav__link">Installation</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../client.html" class="md-nav__link">Clients</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../security.html" class="md-nav__link">Security</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html" class="md-nav__link">Administration</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html#event-listeners" class="md-nav__link">Event listeners</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../optimizer.html" class="md-nav__link">Query optimizer</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../connector.html" class="md-nav__link">Connectors</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../functions.html" class="md-nav__link">Functions and operators</a>
      <ul class="md-nav__list"> 
    <li class="md-nav__item">
    
    
      <a href="aggregate.html" class="md-nav__link">Aggregate</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="array.html" class="md-nav__link">Array</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="binary.html" class="md-nav__link">Binary</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="bitwise.html" class="md-nav__link">Bitwise</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="color.html" class="md-nav__link">Color</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="comparison.html" class="md-nav__link">Comparison</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conditional.html" class="md-nav__link">Conditional</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conversion.html" class="md-nav__link">Conversion</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="datetime.html" class="md-nav__link">Date and time</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="decimal.html" class="md-nav__link">Decimal</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="geospatial.html" class="md-nav__link">Geospatial</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="hyperloglog.html" class="md-nav__link">HyperLogLog</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ipaddress.html" class="md-nav__link">IP Address</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="json.html" class="md-nav__link">JSON</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="lambda.html" class="md-nav__link">Lambda</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="logical.html" class="md-nav__link">Logical</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ml.html" class="md-nav__link">Machine learning</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
    <input class="md-toggle md-nav__toggle" data-md-toggle="toc" type="checkbox" id="__toc">
    <label class="md-nav__link md-nav__link--active" for="__toc"> Map </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">Map</a>
      
        
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#subscript-operator" class="md-nav__link">Subscript operator: []</a>
        </li>
        <li class="md-nav__item"><a href="#map-functions" class="md-nav__link">Map functions</a>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="math.html" class="md-nav__link">Math</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="qdigest.html" class="md-nav__link">Quantile digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="regexp.html" class="md-nav__link">Regular expression</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="session.html" class="md-nav__link">Session</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="setdigest.html" class="md-nav__link">Set Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="string.html" class="md-nav__link">String</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="system.html" class="md-nav__link">System</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="teradata.html" class="md-nav__link">Teradata</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="tdigest.html" class="md-nav__link">T-Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="url.html" class="md-nav__link">URL</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="uuid.html" class="md-nav__link">UUID</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="window.html" class="md-nav__link">Window</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list.html" class="md-nav__link">List of functions and operators</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list-by-topic.html" class="md-nav__link">List of functions by topic</a>
      
    
    </li></ul>
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../language.html" class="md-nav__link">SQL language</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../sql.html" class="md-nav__link">SQL statement syntax</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../develop.html" class="md-nav__link">Developer guide</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../appendix.html" class="md-nav__link">Appendix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../release.html" class="md-nav__link">Release notes</a>
      
    
    </li>
  </ul>
  

</nav>
              </div>
            </div>
          </div>
          <div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#subscript-operator" class="md-nav__link">Subscript operator: []</a>
        </li>
        <li class="md-nav__item"><a href="#map-functions" class="md-nav__link">Map functions</a>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  
<h1 id="functions-map--page-root">Map functions and operators<a class="headerlink" href="#functions-map--page-root" title="Permalink to this headline">#</a></h1>

<h2 id="subscript-operator">Subscript operator: []<a class="headerlink" href="#subscript-operator" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">[]</span></code> operator is used to retrieve the value corresponding to a given key from a map:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name_to_age_map</span><span class="p">[</span><span class="s1">'Bob'</span><span class="p">]</span> <span class="k">AS</span> <span class="n">bob_age</span><span class="p">;</span>
</pre></div>
</div>


<h2 id="map-functions">Map functions<a class="headerlink" href="#map-functions" title="Permalink to this headline">#</a></h2>
<dl class="py function">
<dt>
<code class="sig-name descname">cardinality</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">x</span></em><span class="sig-paren">)</span> → bigint</dt>
<dd><p>Returns the cardinality (size) of the map <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt>
<code class="sig-name descname">element_at</code><span class="sig-paren">(</span><em class="sig-param">map(K</em>, <em class="sig-param">V)</em>, <em class="sig-param">key</em><span class="sig-paren">)</span> → V</dt>
<dd><p>Returns value for given <code class="docutils literal notranslate"><span class="pre">key</span></code>, or <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if the key is not contained in the map.</p>
</dd></dl>
<dl class="py function">
<dt id="map">
<code class="sig-name descname">map</code><span class="sig-paren">(</span><span class="sig-paren">)</span> → map&lt;unknown, unknown&gt;<a class="headerlink" href="#map" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an empty map.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">map</span><span class="p">();</span>
<span class="c1">-- {}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt>
<code class="sig-name descname">map</code><span class="sig-paren">(</span><em class="sig-param">array(K)</em>, <em class="sig-param">array(V)) -&gt; map(K</em>, <em class="sig-param">V</em><span class="sig-paren">)</span></dt>
<dd><p>Returns a map created using the given key/value arrays.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">map</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">]);</span>
<span class="c1">-- {1 -&gt; 2, 3 -&gt; 4}</span>
</pre></div>
</div>
<p>See also <a class="reference internal" href="aggregate.html#map_agg" title="map_agg"><code class="xref py py-func docutils literal notranslate"><span class="pre">map_agg()</span></code></a> and <a class="reference internal" href="aggregate.html#multimap_agg" title="multimap_agg"><code class="xref py py-func docutils literal notranslate"><span class="pre">multimap_agg()</span></code></a> for creating a map as an aggregation.</p>
</dd></dl>
<dl class="py function">
<dt id="map_from_entries">
<code class="sig-name descname">map_from_entries</code><span class="sig-paren">(</span><em class="sig-param">array(row(K</em>, <em class="sig-param">V))) -&gt; map(K</em>, <em class="sig-param">V</em><span class="sig-paren">)</span><a class="headerlink" href="#map_from_entries" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a map created from the given array of entries.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">map_from_entries</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'x'</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">)]);</span>
<span class="c1">-- {1 -&gt; 'x', 2 -&gt; 'y'}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="multimap_from_entries">
<code class="sig-name descname">multimap_from_entries</code><span class="sig-paren">(</span><em class="sig-param">array(row(K</em>, <em class="sig-param">V))) -&gt; map(K</em>, <em class="sig-param">array(V)</em><span class="sig-paren">)</span><a class="headerlink" href="#multimap_from_entries" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a multimap created from the given array of entries. Each key can be associated with multiple values.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">multimap_from_entries</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'x'</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'z'</span><span class="p">)]);</span>
<span class="c1">-- {1 -&gt; ['x', 'z'], 2 -&gt; ['y']}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="map_entries">
<code class="sig-name descname">map_entries</code><span class="sig-paren">(</span><em class="sig-param">map(K</em>, <em class="sig-param">V)) -&gt; array(row(K</em>, <em class="sig-param">V)</em><span class="sig-paren">)</span><a class="headerlink" href="#map_entries" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array of all entries in the given map.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">map_entries</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'x'</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">]));</span>
<span class="c1">-- [ROW(1, 'x'), ROW(2, 'y')]</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="map_concat">
<code class="sig-name descname">map_concat</code><span class="sig-paren">(</span><em class="sig-param">map1(K</em>, <em class="sig-param">V)</em>, <em class="sig-param">map2(K</em>, <em class="sig-param">V)</em>, <em class="sig-param">...</em>, <em class="sig-param">mapN(K</em>, <em class="sig-param">V)) -&gt; map(K</em>, <em class="sig-param">V</em><span class="sig-paren">)</span><a class="headerlink" href="#map_concat" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the union of all the given maps. If a key is found in multiple given maps,
that key’s value in the resulting map comes from the last one of those maps.</p>
</dd></dl>
<dl class="py function">
<dt id="map_filter">
<code class="sig-name descname">map_filter</code><span class="sig-paren">(</span><em class="sig-param">map(K</em>, <em class="sig-param">V)</em>, <em class="sig-param">function(K</em>, <em class="sig-param">V</em>, <em class="sig-param">boolean)) -&gt; map(K</em>, <em class="sig-param">V</em><span class="sig-paren">)</span><a class="headerlink" href="#map_filter" title="Permalink to this definition">#</a></dt>
<dd><p>Constructs a map from those entries of <code class="docutils literal notranslate"><span class="pre">map</span></code> for which <code class="docutils literal notranslate"><span class="pre">function</span></code> returns true:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">map_filter</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="nb">ARRAY</span><span class="p">[]),</span> <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">true</span><span class="p">);</span>
<span class="c1">-- {}</span>

<span class="k">SELECT</span> <span class="n">map_filter</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">10</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="mi">30</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">]),</span>
                  <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">v</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">);</span>
<span class="c1">-- {10 -&gt; a, 30 -&gt; c}</span>

<span class="k">SELECT</span> <span class="n">map_filter</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'k1'</span><span class="p">,</span> <span class="s1">'k2'</span><span class="p">,</span> <span class="s1">'k3'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">20</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">15</span><span class="p">]),</span>
                  <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">v</span> <span class="o">&gt;</span> <span class="mi">10</span><span class="p">);</span>
<span class="c1">-- {k1 -&gt; 20, k3 -&gt; 15}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="map_keys">
<code class="sig-name descname">map_keys</code><span class="sig-paren">(</span><em class="sig-param">x(K</em>, <em class="sig-param">V)) -&gt; array(K</em><span class="sig-paren">)</span><a class="headerlink" href="#map_keys" title="Permalink to this definition">#</a></dt>
<dd><p>Returns all the keys in the map <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt id="map_values">
<code class="sig-name descname">map_values</code><span class="sig-paren">(</span><em class="sig-param">x(K</em>, <em class="sig-param">V)) -&gt; array(V</em><span class="sig-paren">)</span><a class="headerlink" href="#map_values" title="Permalink to this definition">#</a></dt>
<dd><p>Returns all the values in the map <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt id="map_zip_with">
<code class="sig-name descname">map_zip_with</code><span class="sig-paren">(</span><em class="sig-param">map(K</em>, <em class="sig-param">V1)</em>, <em class="sig-param">map(K</em>, <em class="sig-param">V2)</em>, <em class="sig-param">function(K</em>, <em class="sig-param">V1</em>, <em class="sig-param">V2</em>, <em class="sig-param">V3)) -&gt; map(K</em>, <em class="sig-param">V3</em><span class="sig-paren">)</span><a class="headerlink" href="#map_zip_with" title="Permalink to this definition">#</a></dt>
<dd><p>Merges the two given maps into a single map by applying <code class="docutils literal notranslate"><span class="pre">function</span></code> to the pair of values with the same key.
For keys only presented in one map, NULL will be passed as the value for the missing key.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">map_zip_with</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">]),</span>
                    <span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'d'</span><span class="p">,</span> <span class="s1">'e'</span><span class="p">,</span> <span class="s1">'f'</span><span class="p">]),</span>
                    <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v1</span><span class="p">,</span> <span class="n">v2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">concat</span><span class="p">(</span><span class="n">v1</span><span class="p">,</span> <span class="n">v2</span><span class="p">));</span>
<span class="c1">-- {1 -&gt; ad, 2 -&gt; be, 3 -&gt; cf}</span>

<span class="k">SELECT</span> <span class="n">map_zip_with</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'k1'</span><span class="p">,</span> <span class="s1">'k2'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">]),</span>
                    <span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'k2'</span><span class="p">,</span> <span class="s1">'k3'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">4</span><span class="p">,</span> <span class="mi">9</span><span class="p">]),</span>
                    <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v1</span><span class="p">,</span> <span class="n">v2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">(</span><span class="n">v1</span><span class="p">,</span> <span class="n">v2</span><span class="p">));</span>
<span class="c1">-- {k1 -&gt; ROW(1, null), k2 -&gt; ROW(2, 4), k3 -&gt; ROW(null, 9)}</span>

<span class="k">SELECT</span> <span class="n">map_zip_with</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">27</span><span class="p">]),</span>
                    <span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]),</span>
                    <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v1</span><span class="p">,</span> <span class="n">v2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">||</span> <span class="k">CAST</span><span class="p">(</span><span class="n">v1</span> <span class="o">/</span> <span class="n">v2</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">));</span>
<span class="c1">-- {a -&gt; a1, b -&gt; b4, c -&gt; c9}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="transform_keys">
<code class="sig-name descname">transform_keys</code><span class="sig-paren">(</span><em class="sig-param">map(K1</em>, <em class="sig-param">V)</em>, <em class="sig-param">function(K1</em>, <em class="sig-param">V</em>, <em class="sig-param">K2)) -&gt; map(K2</em>, <em class="sig-param">V</em><span class="sig-paren">)</span><a class="headerlink" href="#transform_keys" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a map that applies <code class="docutils literal notranslate"><span class="pre">function</span></code> to each entry of <code class="docutils literal notranslate"><span class="pre">map</span></code> and transforms the keys:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">transform_keys</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="nb">ARRAY</span><span class="p">[]),</span> <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">+</span> <span class="mi">1</span><span class="p">);</span>
<span class="c1">-- {}</span>

<span class="k">SELECT</span> <span class="n">transform_keys</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">]),</span>
                      <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">+</span> <span class="mi">1</span><span class="p">);</span>
<span class="c1">-- {2 -&gt; a, 3 -&gt; b, 4 -&gt; c}</span>

<span class="k">SELECT</span> <span class="n">transform_keys</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]),</span>
                      <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">v</span> <span class="o">*</span> <span class="n">v</span><span class="p">);</span>
<span class="c1">-- {1 -&gt; 1, 4 -&gt; 2, 9 -&gt; 3}</span>

<span class="k">SELECT</span> <span class="n">transform_keys</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">]),</span>
                      <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">||</span> <span class="k">CAST</span><span class="p">(</span><span class="n">v</span> <span class="k">as</span> <span class="nb">VARCHAR</span><span class="p">));</span>
<span class="c1">-- {a1 -&gt; 1, b2 -&gt; 2}</span>

<span class="k">SELECT</span> <span class="n">transform_keys</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="p">,</span> <span class="mi">1</span><span class="p">.</span><span class="mi">4</span><span class="p">]),</span>
                      <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'one'</span><span class="p">,</span> <span class="s1">'two'</span><span class="p">])[</span><span class="n">k</span><span class="p">]);</span>
<span class="c1">-- {one -&gt; 1.0, two -&gt; 1.4}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="transform_values">
<code class="sig-name descname">transform_values</code><span class="sig-paren">(</span><em class="sig-param">map(K</em>, <em class="sig-param">V1)</em>, <em class="sig-param">function(K</em>, <em class="sig-param">V1</em>, <em class="sig-param">V2)) -&gt; map(K</em>, <em class="sig-param">V2</em><span class="sig-paren">)</span><a class="headerlink" href="#transform_values" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a map that applies <code class="docutils literal notranslate"><span class="pre">function</span></code> to each entry of <code class="docutils literal notranslate"><span class="pre">map</span></code> and transforms the values:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">transform_values</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="nb">ARRAY</span><span class="p">[]),</span> <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">v</span> <span class="o">+</span> <span class="mi">1</span><span class="p">);</span>
<span class="c1">-- {}</span>

<span class="k">SELECT</span> <span class="n">transform_values</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">10</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="mi">30</span><span class="p">]),</span>
                        <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">v</span> <span class="o">+</span> <span class="n">k</span><span class="p">);</span>
<span class="c1">-- {1 -&gt; 11, 2 -&gt; 22, 3 -&gt; 33}</span>

<span class="k">SELECT</span> <span class="n">transform_values</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">]),</span>
                        <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">*</span> <span class="n">k</span><span class="p">);</span>
<span class="c1">-- {1 -&gt; 1, 2 -&gt; 4, 3 -&gt; 9}</span>

<span class="k">SELECT</span> <span class="n">transform_values</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">]),</span>
                        <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">k</span> <span class="o">||</span> <span class="k">CAST</span><span class="p">(</span><span class="n">v</span> <span class="k">as</span> <span class="nb">VARCHAR</span><span class="p">));</span>
<span class="c1">-- {a -&gt; a1, b -&gt; b2}</span>

<span class="k">SELECT</span> <span class="n">transform_values</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="nb">ARRAY</span> <span class="p">[</span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="p">,</span> <span class="mi">1</span><span class="p">.</span><span class="mi">4</span><span class="p">]),</span>
                        <span class="p">(</span><span class="n">k</span><span class="p">,</span> <span class="n">v</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'one'</span><span class="p">,</span> <span class="s1">'two'</span><span class="p">])[</span><span class="n">k</span><span class="p">]</span>
                          <span class="o">||</span> <span class="s1">'_'</span> <span class="o">||</span> <span class="k">CAST</span><span class="p">(</span><span class="n">v</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">));</span>
<span class="c1">-- {1 -&gt; one_1.0, 2 -&gt; two_1.4}</span>
</pre></div>
</div>
</dd></dl>


          </article>
        </div>
      </div>
    </main>
  </div>
  <footer class="md-footer">
    <div class="md-footer-nav">
      <nav class="md-footer-nav__inner md-grid">
          
            <a href="ml.html" title="Machine learning functions"
               class="md-flex md-footer-nav__link md-footer-nav__link--prev"
               rel="prev">
              <div class="md-flex__cell md-flex__cell--shrink">
                <i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
              </div>
              <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
                <span class="md-flex__ellipsis">
                  <span
                      class="md-footer-nav__direction"> Previous </span> Machine learning functions </span>
              </div>
            </a>
          
          
            <a href="math.html" title="Mathematical functions and operators"
               class="md-flex md-footer-nav__link md-footer-nav__link--next"
               rel="next">
            <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title"><span
                class="md-flex__ellipsis"> <span
                class="md-footer-nav__direction"> Next </span> Mathematical functions and operators </span>
            </div>
            <div class="md-flex__cell md-flex__cell--shrink"><i
                class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
            </div>
          
        </a>
        
      </nav>
    </div>
    <div class="md-footer-meta md-typeset">
      <div class="md-footer-meta__inner md-grid">
        <div class="md-footer-copyright">
        </div>
      </div>
    </div>
  </footer>
  <script src="../_static/javascripts/application.js"></script>
  <script>app.initialize({version: "1.0.4", url: {base: ".."}})</script>
  </body>
</html>