345/functions/array.html


<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta charset="utf-8" />
  <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">

    <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>Array Functions and Operators &#8212; Presto 345 Documentation</title>
    <link rel="stylesheet" href="../_static/material.css" type="text/css" />
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <link rel="stylesheet" type="text/css" href="../_static/presto.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>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Map Functions and Operators" href="map.html" />
    <link rel="prev" title="Window Functions" href="window.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');
    </script>
  </head>
  <body dir=ltr
        data-md-color-primary=blue-grey data-md-color-accent=light-blue>
  
  <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/array" tabindex="1" class="md-skip"> Skip to content </a>
  <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="../index.html" title="Presto 345 Documentation"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/presto.svg" height="26"
                   alt="Presto 345 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">Presto 345 Documentation</span>
          <span class="md-header-nav__topic"> Array 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/prestosql/presto" 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">
    Presto
  </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="../index.html" title="Presto 345 Documentation" class="md-nav__button md-logo">
      
        <img src="../_static/presto.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Presto 345 Documentation">Presto 345 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/prestosql/presto" 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">
    Presto
  </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="../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="logical.html" class="md-nav__link">Logical</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="lambda.html" class="md-nav__link">Lambda</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="math.html" class="md-nav__link">Math</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="decimal.html" class="md-nav__link">Decimal</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="regexp.html" class="md-nav__link">Regular Expression</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="json.html" class="md-nav__link">JSON</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="aggregate.html" class="md-nav__link">Aggregate</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="window.html" class="md-nav__link">Window</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"> Array </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">Array</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="#concatenation-operator" class="md-nav__link">Concatenation Operator: ||</a>
        </li>
        <li class="md-nav__item"><a href="#array-functions" class="md-nav__link">Array Functions</a>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="map.html" class="md-nav__link">Map</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="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="qdigest.html" class="md-nav__link">Quantile Digest</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="uuid.html" class="md-nav__link">UUID</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="session.html" class="md-nav__link">Session</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="ml.html" class="md-nav__link">Machine Learning</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list.html" class="md-nav__link">List of Functions and Operators</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="../migration.html" class="md-nav__link">Migration</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="../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="#concatenation-operator" class="md-nav__link">Concatenation Operator: ||</a>
        </li>
        <li class="md-nav__item"><a href="#array-functions" class="md-nav__link">Array Functions</a>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  
<h1 id="functions-array--page-root">Array Functions and Operators<a class="headerlink" href="#functions-array--page-root" title="Permalink to this headline">#</a></h1>

<span id="id1"></span><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 access an element of an array and is indexed starting from one:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">my_array</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="k">AS</span> <span class="n">first_element</span>
</pre></div>
</div>


<span id="id2"></span><h2 id="concatenation-operator">Concatenation Operator: ||<a class="headerlink" href="#concatenation-operator" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">||</span></code> operator is used to concatenate an array with an array or an element of the same type:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">||</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">];</span>
<span class="c1">-- [1, 2]</span>

<span class="k">SELECT</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">||</span> <span class="mi">2</span><span class="p">;</span>
<span class="c1">-- [1, 2]</span>

<span class="k">SELECT</span> <span class="mi">2</span> <span class="o">||</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">];</span>
<span class="c1">-- [2, 1]</span>
</pre></div>
</div>


<h2 id="array-functions">Array Functions<a class="headerlink" href="#array-functions" title="Permalink to this headline">#</a></h2>
<dl class="function">
<dt id="all_match">
<code class="sig-name descname">all_match</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">boolean)</em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#all_match" title="Permalink to this definition">#</a></dt>
<dd><p>Returns whether all elements of an array match the given predicate. Returns <code class="docutils literal notranslate"><span class="pre">true</span></code> if all the elements
match the predicate (a special case is when the array is empty); <code class="docutils literal notranslate"><span class="pre">false</span></code> if one or more elements don’t
match; <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal notranslate"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal notranslate"><span class="pre">true</span></code> for all
other elements.</p>
</dd></dl>
<dl class="function">
<dt id="any_match">
<code class="sig-name descname">any_match</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">boolean)</em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#any_match" title="Permalink to this definition">#</a></dt>
<dd><p>Returns whether any elements of an array match the given predicate. Returns <code class="docutils literal notranslate"><span class="pre">true</span></code> if one or more
elements match the predicate; <code class="docutils literal notranslate"><span class="pre">false</span></code> if none of the elements matches (a special case is when the
array is empty); <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal notranslate"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal notranslate"><span class="pre">false</span></code>
for all other elements.</p>
</dd></dl>
<dl class="function">
<dt id="array_distinct">
<code class="sig-name descname">array_distinct</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_distinct" title="Permalink to this definition">#</a></dt>
<dd><p>Remove duplicate values from the array <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="array_intersect">
<code class="sig-name descname">array_intersect</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">y</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_intersect" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array of the elements in the intersection of <code class="docutils literal notranslate"><span class="pre">x</span></code> and <code class="docutils literal notranslate"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>
<dl class="function">
<dt id="array_union">
<code class="sig-name descname">array_union</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">y</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_union" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array of the elements in the union of <code class="docutils literal notranslate"><span class="pre">x</span></code> and <code class="docutils literal notranslate"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>
<dl class="function">
<dt id="array_except">
<code class="sig-name descname">array_except</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">y</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_except" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array of elements in <code class="docutils literal notranslate"><span class="pre">x</span></code> but not in <code class="docutils literal notranslate"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>
<dl class="function">
<dt id="array_join">
<code class="sig-name descname">array_join</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">delimiter</em>, <em class="sig-param">null_replacement</em><span class="sig-paren">)</span> → varchar<a class="headerlink" href="#array_join" title="Permalink to this definition">#</a></dt>
<dd><p>Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.</p>
</dd></dl>
<dl class="function">
<dt id="array_max">
<code class="sig-name descname">array_max</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → x<a class="headerlink" href="#array_max" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the maximum value of input array.</p>
</dd></dl>
<dl class="function">
<dt id="array_min">
<code class="sig-name descname">array_min</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → x<a class="headerlink" href="#array_min" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the minimum value of input array.</p>
</dd></dl>
<dl class="function">
<dt id="array_position">
<code class="sig-name descname">array_position</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">element</em><span class="sig-paren">)</span> → bigint<a class="headerlink" href="#array_position" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the position of the first occurrence of the <code class="docutils literal notranslate"><span class="pre">element</span></code> in array <code class="docutils literal notranslate"><span class="pre">x</span></code> (or 0 if not found).</p>
</dd></dl>
<dl class="function">
<dt id="array_remove">
<code class="sig-name descname">array_remove</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">element</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_remove" title="Permalink to this definition">#</a></dt>
<dd><p>Remove all elements that equal <code class="docutils literal notranslate"><span class="pre">element</span></code> from array <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="array_sort">
<code class="sig-name descname">array_sort</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#array_sort" title="Permalink to this definition">#</a></dt>
<dd><p>Sorts and returns the array <code class="docutils literal notranslate"><span class="pre">x</span></code>. The elements of <code class="docutils literal notranslate"><span class="pre">x</span></code> must be orderable.
Null elements will be placed at the end of the returned array.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">array_sort</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">T</em>, <em class="sig-param">int)) -&gt; array(T</em><span class="sig-paren">)</span></dt>
<dd><p>Sorts and returns the <code class="docutils literal notranslate"><span class="pre">array</span></code> based on the given comparator <code class="docutils literal notranslate"><span class="pre">function</span></code>.
The comparator will take two nullable arguments representing two nullable
elements of the <code class="docutils literal notranslate"><span class="pre">array</span></code>. It returns -1, 0, or 1 as the first nullable
element is less than, equal to, or greater than the second nullable element.
If the comparator function returns other values (including <code class="docutils literal notranslate"><span class="pre">NULL</span></code>), the
query will fail and raise an error.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">array_sort</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">5</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">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="n">x</span> <span class="o">&lt;</span> <span class="n">y</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="k">IF</span><span class="p">(</span><span class="n">x</span> <span class="o">=</span> <span class="n">y</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="o">-</span><span class="mi">1</span><span class="p">)));</span>
<span class="c1">-- [5, 3, 2, 2, 1]</span>

<span class="k">SELECT</span> <span class="n">array_sort</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'bc'</span><span class="p">,</span> <span class="s1">'ab'</span><span class="p">,</span> <span class="s1">'dc'</span><span class="p">],</span>
                  <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="n">x</span> <span class="o">&lt;</span> <span class="n">y</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="k">IF</span><span class="p">(</span><span class="n">x</span> <span class="o">=</span> <span class="n">y</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="o">-</span><span class="mi">1</span><span class="p">)));</span>
<span class="c1">-- ['dc', 'bc', 'ab']</span>


<span class="k">SELECT</span> <span class="n">array_sort</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="k">null</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="c1">-- sort null first with descending order</span>
                  <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">x</span> <span class="k">IS</span> <span class="k">NULL</span> <span class="k">THEN</span> <span class="o">-</span><span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">y</span> <span class="k">IS</span> <span class="k">NULL</span> <span class="k">THEN</span> <span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">x</span> <span class="o">&lt;</span> <span class="n">y</span> <span class="k">THEN</span> <span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">x</span> <span class="o">=</span> <span class="n">y</span> <span class="k">THEN</span> <span class="mi">0</span>
                                 <span class="k">ELSE</span> <span class="o">-</span><span class="mi">1</span> <span class="k">END</span><span class="p">);</span>
<span class="c1">-- [null, null, 5, 3, 2, 2, 1]</span>

<span class="k">SELECT</span> <span class="n">array_sort</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="k">null</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="c1">-- sort null last with descending order</span>
                  <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">x</span> <span class="k">IS</span> <span class="k">NULL</span> <span class="k">THEN</span> <span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">y</span> <span class="k">IS</span> <span class="k">NULL</span> <span class="k">THEN</span> <span class="o">-</span><span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">x</span> <span class="o">&lt;</span> <span class="n">y</span> <span class="k">THEN</span> <span class="mi">1</span>
                                 <span class="k">WHEN</span> <span class="n">x</span> <span class="o">=</span> <span class="n">y</span> <span class="k">THEN</span> <span class="mi">0</span>
                                 <span class="k">ELSE</span> <span class="o">-</span><span class="mi">1</span> <span class="k">END</span><span class="p">);</span>
<span class="c1">-- [5, 3, 2, 2, 1, null, null]</span>

<span class="k">SELECT</span> <span class="n">array_sort</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">'abcd'</span><span class="p">,</span> <span class="s1">'abc'</span><span class="p">],</span>
                  <span class="c1">-- sort by string length</span>
                  <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="k">length</span><span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="o">&lt;</span> <span class="k">length</span><span class="p">(</span><span class="n">y</span><span class="p">),</span> <span class="o">-</span><span class="mi">1</span><span class="p">,</span>
                               <span class="k">IF</span><span class="p">(</span><span class="k">length</span><span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="o">=</span> <span class="k">length</span><span class="p">(</span><span class="n">y</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="c1">-- ['a', 'abc', 'abcd']</span>

<span class="k">SELECT</span> <span class="n">array_sort</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="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">1</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">2</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="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="c1">-- sort by array length</span>
                  <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="k">cardinality</span><span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="o">&lt;</span> <span class="k">cardinality</span><span class="p">(</span><span class="n">y</span><span class="p">),</span> <span class="o">-</span><span class="mi">1</span><span class="p">,</span>
                               <span class="k">IF</span><span class="p">(</span><span class="k">cardinality</span><span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="o">=</span> <span class="k">cardinality</span><span class="p">(</span><span class="n">y</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="c1">-- [[1, 2], [2, 3, 1], [4, 2, 1, 4]]</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="arrays_overlap">
<code class="sig-name descname">arrays_overlap</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">y</em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#arrays_overlap" title="Permalink to this definition">#</a></dt>
<dd><p>Tests if arrays <code class="docutils literal notranslate"><span class="pre">x</span></code> and <code class="docutils literal notranslate"><span class="pre">y</span></code> have any any non-null elements in common.
Returns null if there are no non-null elements in common but either array contains null.</p>
</dd></dl>
<dl class="function">
<dt id="cardinality">
<code class="sig-name descname">cardinality</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → bigint<a class="headerlink" href="#cardinality" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the cardinality (size) of the array <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">concat</code><span class="sig-paren">(</span><em class="sig-param">array1</em>, <em class="sig-param">array2</em>, <em class="sig-param">...</em>, <em class="sig-param">arrayN</em><span class="sig-paren">)</span> → array</dt>
<dd><p>Concatenates the arrays <code class="docutils literal notranslate"><span class="pre">array1</span></code>, <code class="docutils literal notranslate"><span class="pre">array2</span></code>, <code class="docutils literal notranslate"><span class="pre">...</span></code>, <code class="docutils literal notranslate"><span class="pre">arrayN</span></code>.
This function provides the same functionality as the SQL-standard concatenation operator (<code class="docutils literal notranslate"><span class="pre">||</span></code>).</p>
</dd></dl>
<dl class="function">
<dt id="combinations">
<code class="sig-name descname">combinations</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">n) -&gt; array(array(T)</em><span class="sig-paren">)</span><a class="headerlink" href="#combinations" title="Permalink to this definition">#</a></dt>
<dd><p>Returns n-element subgroups of input array. If the input array has no duplicates,
<code class="docutils literal notranslate"><span class="pre">combinations</span></code> returns n-element subsets.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">combinations</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'foo'</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">,</span> <span class="s1">'baz'</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span>
<span class="c1">-- [['foo', 'bar'], ['foo', 'baz'], ['bar', 'baz']]</span>

<span class="k">SELECT</span> <span class="n">combinations</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="mi">2</span><span class="p">);</span>
<span class="c1">-- [[1, 2], [1, 3], [2, 3]]</span>

<span class="k">SELECT</span> <span class="n">combinations</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">2</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span>
<span class="c1">-- [[1, 2], [1, 2], [2, 2]]</span>
</pre></div>
</div>
<p>Order of subgroups is deterministic but unspecified. Order of elements within
a subgroup deterministic but unspecified. <code class="docutils literal notranslate"><span class="pre">n</span></code> must be not be greater than 5,
and the total size of subgroups generated must be smaller than 100,000.</p>
</dd></dl>
<dl class="function">
<dt id="contains">
<code class="sig-name descname">contains</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">element</em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#contains" title="Permalink to this definition">#</a></dt>
<dd><p>Returns true if the array <code class="docutils literal notranslate"><span class="pre">x</span></code> contains the <code class="docutils literal notranslate"><span class="pre">element</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="element_at">
<code class="sig-name descname">element_at</code><span class="sig-paren">(</span><em class="sig-param">array(E)</em>, <em class="sig-param">index</em><span class="sig-paren">)</span> → E<a class="headerlink" href="#element_at" title="Permalink to this definition">#</a></dt>
<dd><p>Returns element of <code class="docutils literal notranslate"><span class="pre">array</span></code> at given <code class="docutils literal notranslate"><span class="pre">index</span></code>.
If <code class="docutils literal notranslate"><span class="pre">index</span></code> &gt; 0, this function provides the same functionality as the SQL-standard subscript operator (<code class="docutils literal notranslate"><span class="pre">[]</span></code>),
except that the function returns <code class="docutils literal notranslate"><span class="pre">NULL</span></code> when accessing an <code class="docutils literal notranslate"><span class="pre">index</span></code> larger than array length, whereas
the subscript operator would fail in such a case.
If <code class="docutils literal notranslate"><span class="pre">index</span></code> &lt; 0, <code class="docutils literal notranslate"><span class="pre">element_at</span></code> accesses elements from the last to the first.</p>
</dd></dl>
<dl class="function">
<dt id="filter">
<code class="sig-name descname">filter</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">boolean)) -&gt; array(T</em><span class="sig-paren">)</span><a class="headerlink" href="#filter" title="Permalink to this definition">#</a></dt>
<dd><p>Constructs an array from those elements of <code class="docutils literal notranslate"><span class="pre">array</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">filter</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="n">x</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">filter</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="o">-</span><span class="mi">6</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">7</span><span class="p">],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="o">&gt;</span> <span class="mi">0</span><span class="p">);</span>
<span class="c1">-- [5, 7]</span>

<span class="k">SELECT</span> <span class="n">filter</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="k">NULL</span><span class="p">],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">);</span>
<span class="c1">-- [5, 7]</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="flatten">
<code class="sig-name descname">flatten</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#flatten" title="Permalink to this definition">#</a></dt>
<dd><p>Flattens an <code class="docutils literal notranslate"><span class="pre">array(array(T))</span></code> to an <code class="docutils literal notranslate"><span class="pre">array(T)</span></code> by concatenating the contained arrays.</p>
</dd></dl>
<dl class="function">
<dt id="ngrams">
<code class="sig-name descname">ngrams</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">n) -&gt; array(array(T)</em><span class="sig-paren">)</span><a class="headerlink" href="#ngrams" title="Permalink to this definition">#</a></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">n</span></code>-grams (sub-sequences of adjacent <code class="docutils literal notranslate"><span class="pre">n</span></code> elements) for the <code class="docutils literal notranslate"><span class="pre">array</span></code>.
The order of the <code class="docutils literal notranslate"><span class="pre">n</span></code>-grams in the result is unspecified.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">ngrams</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'foo'</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">,</span> <span class="s1">'baz'</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span>
<span class="c1">-- [['foo', 'bar'], ['bar', 'baz'], ['baz', 'foo']]</span>

<span class="k">SELECT</span> <span class="n">ngrams</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'foo'</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">,</span> <span class="s1">'baz'</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">],</span> <span class="mi">3</span><span class="p">);</span>
<span class="c1">-- [['foo', 'bar', 'baz'], ['bar', 'baz', 'foo']]</span>

<span class="k">SELECT</span> <span class="n">ngrams</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'foo'</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">,</span> <span class="s1">'baz'</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">],</span> <span class="mi">4</span><span class="p">);</span>
<span class="c1">-- [['foo', 'bar', 'baz', 'foo']]</span>

<span class="k">SELECT</span> <span class="n">ngrams</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'foo'</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">,</span> <span class="s1">'baz'</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">],</span> <span class="mi">5</span><span class="p">);</span>
<span class="c1">-- [['foo', 'bar', 'baz', 'foo']]</span>

<span class="k">SELECT</span> <span class="n">ngrams</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="mi">4</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span>
<span class="c1">-- [[1, 2], [2, 3], [3, 4]]</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="none_match">
<code class="sig-name descname">none_match</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">boolean)</em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#none_match" title="Permalink to this definition">#</a></dt>
<dd><p>Returns whether no elements of an array match the given predicate. Returns <code class="docutils literal notranslate"><span class="pre">true</span></code> if none of the elements
matches the predicate (a special case is when the array is empty); <code class="docutils literal notranslate"><span class="pre">false</span></code> if one or more elements match;
<code class="docutils literal notranslate"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal notranslate"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal notranslate"><span class="pre">false</span></code> for all other elements.</p>
</dd></dl>
<dl class="function">
<dt id="reduce">
<code class="sig-name descname">reduce</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">initialState S</em>, <em class="sig-param">inputFunction(S</em>, <em class="sig-param">T</em>, <em class="sig-param">S)</em>, <em class="sig-param">outputFunction(S</em>, <em class="sig-param">R)</em><span class="sig-paren">)</span> → R<a class="headerlink" href="#reduce" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a single value reduced from <code class="docutils literal notranslate"><span class="pre">array</span></code>. <code class="docutils literal notranslate"><span class="pre">inputFunction</span></code> will
be invoked for each element in <code class="docutils literal notranslate"><span class="pre">array</span></code> in order. In addition to taking
the element, <code class="docutils literal notranslate"><span class="pre">inputFunction</span></code> takes the current state, initially
<code class="docutils literal notranslate"><span class="pre">initialState</span></code>, and returns the new state. <code class="docutils literal notranslate"><span class="pre">outputFunction</span></code> will be
invoked to turn the final state into the result value. It may be the
identity function (<code class="docutils literal notranslate"><span class="pre">i</span> <span class="pre">-&gt;</span> <span class="pre">i</span></code>).</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="mi">0</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">s</span> <span class="o">+</span> <span class="n">x</span><span class="p">,</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- 0</span>

<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="mi">50</span><span class="p">],</span> <span class="mi">0</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">s</span> <span class="o">+</span> <span class="n">x</span><span class="p">,</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- 75</span>

<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">50</span><span class="p">],</span> <span class="mi">0</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">s</span> <span class="o">+</span> <span class="n">x</span><span class="p">,</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- NULL</span>

<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">50</span><span class="p">],</span> <span class="mi">0</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">s</span> <span class="o">+</span> <span class="n">coalesce</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="mi">0</span><span class="p">),</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- 75</span>

<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">50</span><span class="p">],</span> <span class="mi">0</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="n">x</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">s</span><span class="p">,</span> <span class="n">s</span> <span class="o">+</span> <span class="n">x</span><span class="p">),</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- 75</span>

<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2147483647</span><span class="p">,</span> <span class="mi">1</span><span class="p">],</span> <span class="nb">BIGINT</span> <span class="s1">'0'</span><span class="p">,</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">s</span> <span class="o">+</span> <span class="n">x</span><span class="p">,</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="n">s</span><span class="p">);</span>
<span class="c1">-- 2147483648</span>

<span class="c1">-- calculates arithmetic average</span>
<span class="k">SELECT</span> <span class="n">reduce</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">6</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="k">CAST</span><span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">ROW</span><span class="p">(</span><span class="k">sum</span> <span class="n">DOUBLE</span><span class="p">,</span> <span class="k">count</span> <span class="nb">INTEGER</span><span class="p">)),</span>
              <span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="k">CAST</span><span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="n">x</span> <span class="o">+</span> <span class="n">s</span><span class="p">.</span><span class="k">sum</span><span class="p">,</span> <span class="n">s</span><span class="p">.</span><span class="k">count</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span> <span class="k">AS</span>
                             <span class="k">ROW</span><span class="p">(</span><span class="k">sum</span> <span class="n">DOUBLE</span><span class="p">,</span> <span class="k">count</span> <span class="nb">INTEGER</span><span class="p">)),</span>
              <span class="n">s</span> <span class="o">-&gt;</span> <span class="k">IF</span><span class="p">(</span><span class="n">s</span><span class="p">.</span><span class="k">count</span> <span class="o">=</span> <span class="mi">0</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">s</span><span class="p">.</span><span class="k">sum</span> <span class="o">/</span> <span class="n">s</span><span class="p">.</span><span class="k">count</span><span class="p">));</span>
<span class="c1">-- 10.25</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="repeat">
<code class="sig-name descname">repeat</code><span class="sig-paren">(</span><em class="sig-param">element</em>, <em class="sig-param">count</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#repeat" title="Permalink to this definition">#</a></dt>
<dd><p>Repeat <code class="docutils literal notranslate"><span class="pre">element</span></code> for <code class="docutils literal notranslate"><span class="pre">count</span></code> times.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">reverse</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → array</dt>
<dd><p>Returns an array which has the reversed order of array <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="sequence">
<code class="sig-name descname">sequence</code><span class="sig-paren">(</span><em class="sig-param">start</em>, <em class="sig-param">stop) -&gt; array(bigint</em><span class="sig-paren">)</span><a class="headerlink" href="#sequence" title="Permalink to this definition">#</a></dt>
<dd><p>Generate a sequence of integers from <code class="docutils literal notranslate"><span class="pre">start</span></code> to <code class="docutils literal notranslate"><span class="pre">stop</span></code>, incrementing
by <code class="docutils literal notranslate"><span class="pre">1</span></code> if <code class="docutils literal notranslate"><span class="pre">start</span></code> is less than or equal to <code class="docutils literal notranslate"><span class="pre">stop</span></code>, otherwise <code class="docutils literal notranslate"><span class="pre">-1</span></code>.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">sequence</code><span class="sig-paren">(</span><em class="sig-param">start</em>, <em class="sig-param">stop</em>, <em class="sig-param">step) -&gt; array(bigint</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of integers from <code class="docutils literal notranslate"><span class="pre">start</span></code> to <code class="docutils literal notranslate"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal notranslate"><span class="pre">step</span></code>.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">sequence</code><span class="sig-paren">(</span><em class="sig-param">start</em>, <em class="sig-param">stop) -&gt; array(date</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of dates from <code class="docutils literal notranslate"><span class="pre">start</span></code> date to <code class="docutils literal notranslate"><span class="pre">stop</span></code> date, incrementing
by <code class="docutils literal notranslate"><span class="pre">1</span></code> day if <code class="docutils literal notranslate"><span class="pre">start</span></code> date is less than or equal to <code class="docutils literal notranslate"><span class="pre">stop</span></code> date, otherwise <code class="docutils literal notranslate"><span class="pre">-1</span></code> day.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">sequence</code><span class="sig-paren">(</span><em class="sig-param">start</em>, <em class="sig-param">stop</em>, <em class="sig-param">step) -&gt; array(date</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of dates from <code class="docutils literal notranslate"><span class="pre">start</span></code> to <code class="docutils literal notranslate"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal notranslate"><span class="pre">step</span></code>.
The type of <code class="docutils literal notranslate"><span class="pre">step</span></code> can be either <code class="docutils literal notranslate"><span class="pre">INTERVAL</span> <span class="pre">DAY</span> <span class="pre">TO</span> <span class="pre">SECOND</span></code> or <code class="docutils literal notranslate"><span class="pre">INTERVAL</span> <span class="pre">YEAR</span> <span class="pre">TO</span> <span class="pre">MONTH</span></code>.</p>
</dd></dl>
<dl class="function">
<dt>
<code class="sig-name descname">sequence</code><span class="sig-paren">(</span><em class="sig-param">start</em>, <em class="sig-param">stop</em>, <em class="sig-param">step) -&gt; array(timestamp</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of timestamps from <code class="docutils literal notranslate"><span class="pre">start</span></code> to <code class="docutils literal notranslate"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal notranslate"><span class="pre">step</span></code>.
The type of <code class="docutils literal notranslate"><span class="pre">step</span></code> can be either <code class="docutils literal notranslate"><span class="pre">INTERVAL</span> <span class="pre">DAY</span> <span class="pre">TO</span> <span class="pre">SECOND</span></code> or <code class="docutils literal notranslate"><span class="pre">INTERVAL</span> <span class="pre">YEAR</span> <span class="pre">TO</span> <span class="pre">MONTH</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="shuffle">
<code class="sig-name descname">shuffle</code><span class="sig-paren">(</span><em class="sig-param">x</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#shuffle" title="Permalink to this definition">#</a></dt>
<dd><p>Generate a random permutation of the given array <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="slice">
<code class="sig-name descname">slice</code><span class="sig-paren">(</span><em class="sig-param">x</em>, <em class="sig-param">start</em>, <em class="sig-param">length</em><span class="sig-paren">)</span> → array<a class="headerlink" href="#slice" title="Permalink to this definition">#</a></dt>
<dd><p>Subsets array <code class="docutils literal notranslate"><span class="pre">x</span></code> starting from index <code class="docutils literal notranslate"><span class="pre">start</span></code> (or starting from the end
if <code class="docutils literal notranslate"><span class="pre">start</span></code> is negative) with a length of <code class="docutils literal notranslate"><span class="pre">length</span></code>.</p>
</dd></dl>
<dl class="function">
<dt id="transform">
<code class="sig-name descname">transform</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">U)) -&gt; array(U</em><span class="sig-paren">)</span><a class="headerlink" href="#transform" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array that is the result of applying <code class="docutils literal notranslate"><span class="pre">function</span></code> to each element of <code class="docutils literal notranslate"><span class="pre">array</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">transform</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</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="k">transform</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="mi">6</span><span class="p">],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="o">+</span> <span class="mi">1</span><span class="p">);</span>
<span class="c1">-- [6, 7]</span>

<span class="k">SELECT</span> <span class="k">transform</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">5</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">6</span><span class="p">],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">coalesce</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="o">+</span> <span class="mi">1</span><span class="p">);</span>
<span class="c1">-- [6, 1, 7]</span>

<span class="k">SELECT</span> <span class="k">transform</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">'abc'</span><span class="p">,</span> <span class="s1">'z'</span><span class="p">],</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="o">||</span> <span class="s1">'0'</span><span class="p">);</span>
<span class="c1">-- ['x0', 'abc0', 'z0']</span>

<span class="k">SELECT</span> <span class="k">transform</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="mi">1</span><span class="p">,</span> <span class="k">NULL</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">3</span><span class="p">,</span> <span class="k">NULL</span><span class="p">]],</span>
                 <span class="n">a</span> <span class="o">-&gt;</span> <span class="n">filter</span><span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">x</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">));</span>
<span class="c1">-- [[1, 2], [3]]</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="zip">
<code class="sig-name descname">zip</code><span class="sig-paren">(</span><em class="sig-param">array1, array2[, ...]) -&gt; array(row</em><span class="sig-paren">)</span><a class="headerlink" href="#zip" title="Permalink to this definition">#</a></dt>
<dd><p>Merges the given arrays, element-wise, into a single array of rows. The M-th element of
the N-th argument will be the N-th field of the M-th output element.
If the arguments have an uneven length, missing values are filled with <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">zip</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">'1b'</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="s1">'3b'</span><span class="p">]);</span>
<span class="c1">-- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="zip_with">
<code class="sig-name descname">zip_with</code><span class="sig-paren">(</span><em class="sig-param">array(T)</em>, <em class="sig-param">array(U)</em>, <em class="sig-param">function(T</em>, <em class="sig-param">U</em>, <em class="sig-param">R)) -&gt; array(R</em><span class="sig-paren">)</span><a class="headerlink" href="#zip_with" title="Permalink to this definition">#</a></dt>
<dd><p>Merges the two given arrays, element-wise, into a single array using <code class="docutils literal notranslate"><span class="pre">function</span></code>.
If one array is shorter, nulls are appended at the end to match the length of the
longer array, before applying <code class="docutils literal notranslate"><span class="pre">function</span></code>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">zip_with</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="mi">5</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">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">(</span><span class="n">y</span><span class="p">,</span> <span class="n">x</span><span class="p">));</span>
<span class="c1">-- [ROW('a', 1), ROW('b', 3), ROW('c', 5)]</span>

<span class="k">SELECT</span> <span class="n">zip_with</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">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">],</span>
                <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">x</span> <span class="o">+</span> <span class="n">y</span><span class="p">);</span>
<span class="c1">-- [4, 6]</span>

<span class="k">SELECT</span> <span class="n">zip_with</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="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">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">concat</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">));</span>
<span class="c1">-- ['ad', 'be', 'cf']</span>

<span class="k">SELECT</span> <span class="n">zip_with</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="nb">ARRAY</span><span class="p">[</span><span class="s1">'d'</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="s1">'f'</span><span class="p">],</span>
                <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">coalesce</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">));</span>
<span class="c1">-- ['a', null, 'f']</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="window.html" title="Window 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> Window Functions </span>
              </div>
            </a>
          
          
            <a href="map.html" title="Map 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> Map 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>