317/functions/array.html


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>7.16. Array Functions and Operators &#8212; Presto 317 Documentation</title>
    <link rel="stylesheet" href="../_static/presto.css" type="text/css" />
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '../',
        VERSION:     '317',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true,
        SOURCELINK_SUFFIX: '.txt'
      };
    </script>
    <script type="text/javascript" src="../_static/jquery.js"></script>
    <script type="text/javascript" src="../_static/underscore.js"></script>
    <script type="text/javascript" src="../_static/doctools.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="7.17. Map Functions and Operators" href="map.html" />
    <link rel="prev" title="7.15. 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>
<div class="header">
    <h1 class="heading"><a href="../index.html">
        <span>Presto 317 Documentation</span></a></h1>
    <h2 class="heading"><span>7.16. Array Functions and Operators</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="window.html">7.15. Window Functions</a>
    </span>
    <span class="right">
        <a href="map.html">7.17. Map Functions and Operators</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="array-functions-and-operators">
<h1>7.16. Array Functions and Operators</h1>
<div class="section" id="subscript-operator">
<h2>Subscript Operator: []</h2>
<p>The <code class="docutils literal"><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"><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>
</div>
<div class="section" id="concatenation-operator">
<h2>Concatenation Operator: ||</h2>
<p>The <code class="docutils literal"><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"><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>
</div>
<div class="section" id="array-functions">
<h2>Array Functions</h2>
<dl class="function">
<dt id="all_match">
<code class="descname">all_match</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>boolean)</em><span class="sig-paren">)</span> &#x2192; boolean</dt>
<dd><p>Returns whether all elements of an array match the given predicate. Returns <code class="docutils literal"><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"><span class="pre">false</span></code> if one or more elements don’t
match; <code class="docutils literal"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal"><span class="pre">true</span></code> for all
other elements.</p>
</dd></dl>

<dl class="function">
<dt id="any_match">
<code class="descname">any_match</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>boolean)</em><span class="sig-paren">)</span> &#x2192; boolean</dt>
<dd><p>Returns whether any elements of an array match the given predicate. Returns <code class="docutils literal"><span class="pre">true</span></code> if one or more
elements match the predicate; <code class="docutils literal"><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"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal"><span class="pre">false</span></code>
for all other elements.</p>
</dd></dl>

<dl class="function">
<dt id="array_distinct">
<code class="descname">array_distinct</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Remove duplicate values from the array <code class="docutils literal"><span class="pre">x</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="array_intersect">
<code class="descname">array_intersect</code><span class="sig-paren">(</span><em>x</em>, <em>y</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Returns an array of the elements in the intersection of <code class="docutils literal"><span class="pre">x</span></code> and <code class="docutils literal"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>

<dl class="function">
<dt id="array_union">
<code class="descname">array_union</code><span class="sig-paren">(</span><em>x</em>, <em>y</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Returns an array of the elements in the union of <code class="docutils literal"><span class="pre">x</span></code> and <code class="docutils literal"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>

<dl class="function">
<dt id="array_except">
<code class="descname">array_except</code><span class="sig-paren">(</span><em>x</em>, <em>y</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Returns an array of elements in <code class="docutils literal"><span class="pre">x</span></code> but not in <code class="docutils literal"><span class="pre">y</span></code>, without duplicates.</p>
</dd></dl>

<dl class="function">
<dt id="array_join">
<code class="descname">array_join</code><span class="sig-paren">(</span><em>x</em>, <em>delimiter</em>, <em>null_replacement</em><span class="sig-paren">)</span> &#x2192; varchar</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="descname">array_max</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; x</dt>
<dd><p>Returns the maximum value of input array.</p>
</dd></dl>

<dl class="function">
<dt id="array_min">
<code class="descname">array_min</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; x</dt>
<dd><p>Returns the minimum value of input array.</p>
</dd></dl>

<dl class="function">
<dt id="array_position">
<code class="descname">array_position</code><span class="sig-paren">(</span><em>x</em>, <em>element</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the position of the first occurrence of the <code class="docutils literal"><span class="pre">element</span></code> in array <code class="docutils literal"><span class="pre">x</span></code> (or 0 if not found).</p>
</dd></dl>

<dl class="function">
<dt id="array_remove">
<code class="descname">array_remove</code><span class="sig-paren">(</span><em>x</em>, <em>element</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Remove all elements that equal <code class="docutils literal"><span class="pre">element</span></code> from array <code class="docutils literal"><span class="pre">x</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="array_sort">
<code class="descname">array_sort</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Sorts and returns the array <code class="docutils literal"><span class="pre">x</span></code>. The elements of <code class="docutils literal"><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="descname">array_sort</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>T</em>, <em>int)) -&gt; array(T</em><span class="sig-paren">)</span></dt>
<dd><p>Sorts and returns the <code class="docutils literal"><span class="pre">array</span></code> based on the given comparator <code class="docutils literal"><span class="pre">function</span></code>. The comparator will take
two nullable arguments representing two nullable elements of the <code class="docutils literal"><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"><span class="pre">NULL</span></code>), the query will fail and raise an error</p>
<div class="highlight-sql"><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">&#39;bc&#39;</span><span class="p">,</span> <span class="s1">&#39;ab&#39;</span><span class="p">,</span> <span class="s1">&#39;dc&#39;</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">-- [&#39;dc&#39;, &#39;bc&#39;, &#39;ab&#39;]</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">&#39;a&#39;</span><span class="p">,</span> <span class="s1">&#39;abcd&#39;</span><span class="p">,</span> <span class="s1">&#39;abc&#39;</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">-- [&#39;a&#39;, &#39;abc&#39;, &#39;abcd&#39;]</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="descname">arrays_overlap</code><span class="sig-paren">(</span><em>x</em>, <em>y</em><span class="sig-paren">)</span> &#x2192; boolean</dt>
<dd><p>Tests if arrays <code class="docutils literal"><span class="pre">x</span></code> and <code class="docutils literal"><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="descname">cardinality</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the cardinality (size) of the array <code class="docutils literal"><span class="pre">x</span></code>.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">concat</code><span class="sig-paren">(</span><em>array1</em>, <em>array2</em>, <em>...</em>, <em>arrayN</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Concatenates the arrays <code class="docutils literal"><span class="pre">array1</span></code>, <code class="docutils literal"><span class="pre">array2</span></code>, <code class="docutils literal"><span class="pre">...</span></code>, <code class="docutils literal"><span class="pre">arrayN</span></code>.
This function provides the same functionality as the SQL-standard concatenation operator (<code class="docutils literal"><span class="pre">||</span></code>).</p>
</dd></dl>

<dl class="function">
<dt id="combinations">
<code class="descname">combinations</code><span class="sig-paren">(</span><em>array(T)</em>, <em>n) -&gt; array(array(T)</em><span class="sig-paren">)</span></dt>
<dd><p>Returns n-element subgroups of input array. If the input array has no duplicates,
<code class="docutils literal"><span class="pre">combinations</span></code> returns n-element subsets:</p>
<div class="highlight-sql"><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">&#39;foo&#39;</span><span class="p">,</span> <span class="s1">&#39;bar&#39;</span><span class="p">,</span> <span class="s1">&#39;baz&#39;</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span> <span class="c1">-- [[&#39;foo&#39;, &#39;bar&#39;], [&#39;foo&#39;, &#39;baz&#39;], [&#39;bar&#39;, &#39;baz&#39;]]</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"><span class="pre">n</span></code> must be not be greater than 5,
and the total size of subgroups generated must be smaller than 100000.</p>
</dd></dl>

<dl class="function">
<dt id="contains">
<code class="descname">contains</code><span class="sig-paren">(</span><em>x</em>, <em>element</em><span class="sig-paren">)</span> &#x2192; boolean</dt>
<dd><p>Returns true if the array <code class="docutils literal"><span class="pre">x</span></code> contains the <code class="docutils literal"><span class="pre">element</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="element_at">
<code class="descname">element_at</code><span class="sig-paren">(</span><em>array(E)</em>, <em>index</em><span class="sig-paren">)</span> &#x2192; E</dt>
<dd><p>Returns element of <code class="docutils literal"><span class="pre">array</span></code> at given <code class="docutils literal"><span class="pre">index</span></code>.
If <code class="docutils literal"><span class="pre">index</span></code> &gt; 0, this function provides the same functionality as the SQL-standard subscript operator (<code class="docutils literal"><span class="pre">[]</span></code>).
If <code class="docutils literal"><span class="pre">index</span></code> &lt; 0, <code class="docutils literal"><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="descname">filter</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>boolean)) -&gt; array(T</em><span class="sig-paren">)</span></dt>
<dd><p>Constructs an array from those elements of <code class="docutils literal"><span class="pre">array</span></code> for which <code class="docutils literal"><span class="pre">function</span></code> returns true:</p>
<div class="highlight-sql"><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="descname">flatten</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Flattens an <code class="docutils literal"><span class="pre">array(array(T))</span></code> to an <code class="docutils literal"><span class="pre">array(T)</span></code> by concatenating the contained arrays.</p>
</dd></dl>

<dl class="function">
<dt id="ngrams">
<code class="descname">ngrams</code><span class="sig-paren">(</span><em>array(T)</em>, <em>n) -&gt; array(array(T)</em><span class="sig-paren">)</span></dt>
<dd><p>Returns <code class="docutils literal"><span class="pre">n</span></code>-grams (sub-sequences of adjacent <code class="docutils literal"><span class="pre">n</span></code> elements) for the <code class="docutils literal"><span class="pre">array</span></code>. The order
of the <code class="docutils literal"><span class="pre">n</span></code>-grams in the result is unspecified:</p>
<div class="highlight-sql"><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">&#39;foo&#39;</span><span class="p">,</span> <span class="s1">&#39;bar&#39;</span><span class="p">,</span> <span class="s1">&#39;baz&#39;</span><span class="p">,</span> <span class="s1">&#39;foo&#39;</span><span class="p">],</span> <span class="mi">2</span><span class="p">);</span> <span class="c1">-- [[&#39;foo&#39;, &#39;bar&#39;], [&#39;bar&#39;, &#39;baz&#39;], [&#39;baz&#39;, &#39;foo&#39;]]</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">&#39;foo&#39;</span><span class="p">,</span> <span class="s1">&#39;bar&#39;</span><span class="p">,</span> <span class="s1">&#39;baz&#39;</span><span class="p">,</span> <span class="s1">&#39;foo&#39;</span><span class="p">],</span> <span class="mi">3</span><span class="p">);</span> <span class="c1">-- [[&#39;foo&#39;, &#39;bar&#39;, &#39;baz&#39;], [&#39;bar&#39;, &#39;baz&#39;, &#39;foo&#39;]]</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">&#39;foo&#39;</span><span class="p">,</span> <span class="s1">&#39;bar&#39;</span><span class="p">,</span> <span class="s1">&#39;baz&#39;</span><span class="p">,</span> <span class="s1">&#39;foo&#39;</span><span class="p">],</span> <span class="mi">4</span><span class="p">);</span> <span class="c1">-- [[&#39;foo&#39;, &#39;bar&#39;, &#39;baz&#39;, &#39;foo&#39;]]</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">&#39;foo&#39;</span><span class="p">,</span> <span class="s1">&#39;bar&#39;</span><span class="p">,</span> <span class="s1">&#39;baz&#39;</span><span class="p">,</span> <span class="s1">&#39;foo&#39;</span><span class="p">],</span> <span class="mi">5</span><span class="p">);</span> <span class="c1">-- [[&#39;foo&#39;, &#39;bar&#39;, &#39;baz&#39;, &#39;foo&#39;]]</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="descname">none_match</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>boolean)</em><span class="sig-paren">)</span> &#x2192; boolean</dt>
<dd><p>Returns whether no elements of an array match the given predicate. Returns <code class="docutils literal"><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"><span class="pre">false</span></code> if one or more elements match;
<code class="docutils literal"><span class="pre">NULL</span></code> if the predicate function returns <code class="docutils literal"><span class="pre">NULL</span></code> for one or more elements and <code class="docutils literal"><span class="pre">false</span></code> for all other elements.</p>
</dd></dl>

<dl class="function">
<dt id="reduce">
<code class="descname">reduce</code><span class="sig-paren">(</span><em>array(T)</em>, <em>initialState S</em>, <em>inputFunction(S</em>, <em>T</em>, <em>S)</em>, <em>outputFunction(S</em>, <em>R)</em><span class="sig-paren">)</span> &#x2192; R</dt>
<dd><p>Returns a single value reduced from <code class="docutils literal"><span class="pre">array</span></code>. <code class="docutils literal"><span class="pre">inputFunction</span></code> will
be invoked for each element in <code class="docutils literal"><span class="pre">array</span></code> in order. In addition to taking
the element, <code class="docutils literal"><span class="pre">inputFunction</span></code> takes the current state, initially
<code class="docutils literal"><span class="pre">initialState</span></code>, and returns the new state. <code class="docutils literal"><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"><span class="pre">i</span> <span class="pre">-&gt;</span> <span class="pre">i</span></code>).</p>
<div class="highlight-sql"><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="k">CAST</span> <span class="p">(</span><span class="mi">0</span> <span class="k">AS</span> <span class="nb">BIGINT</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="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="c1">-- calculates arithmetic average: 10.25</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>
</pre></div>
</div>
</dd></dl>

<dl class="function">
<dt id="repeat">
<code class="descname">repeat</code><span class="sig-paren">(</span><em>element</em>, <em>count</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Repeat <code class="docutils literal"><span class="pre">element</span></code> for <code class="docutils literal"><span class="pre">count</span></code> times.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">reverse</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Returns an array which has the reversed order of array <code class="docutils literal"><span class="pre">x</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="sequence">
<code class="descname">sequence</code><span class="sig-paren">(</span><em>start</em>, <em>stop) -&gt; array(bigint</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of integers from <code class="docutils literal"><span class="pre">start</span></code> to <code class="docutils literal"><span class="pre">stop</span></code>, incrementing
by <code class="docutils literal"><span class="pre">1</span></code> if <code class="docutils literal"><span class="pre">start</span></code> is less than or equal to <code class="docutils literal"><span class="pre">stop</span></code>, otherwise <code class="docutils literal"><span class="pre">-1</span></code>.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">sequence</code><span class="sig-paren">(</span><em>start</em>, <em>stop</em>, <em>step) -&gt; array(bigint</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of integers from <code class="docutils literal"><span class="pre">start</span></code> to <code class="docutils literal"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal"><span class="pre">step</span></code>.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">sequence</code><span class="sig-paren">(</span><em>start</em>, <em>stop) -&gt; array(date</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of dates from <code class="docutils literal"><span class="pre">start</span></code> date to <code class="docutils literal"><span class="pre">stop</span></code> date, incrementing
by <code class="docutils literal"><span class="pre">1</span></code> day if <code class="docutils literal"><span class="pre">start</span></code> date is less than or equal to <code class="docutils literal"><span class="pre">stop</span></code> date, otherwise <code class="docutils literal"><span class="pre">-1</span></code> day.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">sequence</code><span class="sig-paren">(</span><em>start</em>, <em>stop</em>, <em>step) -&gt; array(date</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of dates from <code class="docutils literal"><span class="pre">start</span></code> to <code class="docutils literal"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal"><span class="pre">step</span></code>.
The type of <code class="docutils literal"><span class="pre">step</span></code> can be either <code class="docutils literal"><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"><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="descname">sequence</code><span class="sig-paren">(</span><em>start</em>, <em>stop</em>, <em>step) -&gt; array(timestamp</em><span class="sig-paren">)</span></dt>
<dd><p>Generate a sequence of timestamps from <code class="docutils literal"><span class="pre">start</span></code> to <code class="docutils literal"><span class="pre">stop</span></code>, incrementing by <code class="docutils literal"><span class="pre">step</span></code>.
The type of <code class="docutils literal"><span class="pre">step</span></code> can be either <code class="docutils literal"><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"><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="descname">shuffle</code><span class="sig-paren">(</span><em>x</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Generate a random permutation of the given array <code class="docutils literal"><span class="pre">x</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="slice">
<code class="descname">slice</code><span class="sig-paren">(</span><em>x</em>, <em>start</em>, <em>length</em><span class="sig-paren">)</span> &#x2192; array</dt>
<dd><p>Subsets array <code class="docutils literal"><span class="pre">x</span></code> starting from index <code class="docutils literal"><span class="pre">start</span></code> (or starting from the end
if <code class="docutils literal"><span class="pre">start</span></code> is negative) with a length of <code class="docutils literal"><span class="pre">length</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="transform">
<code class="descname">transform</code><span class="sig-paren">(</span><em>array(T)</em>, <em>function(T</em>, <em>U)) -&gt; array(U</em><span class="sig-paren">)</span></dt>
<dd><p>Returns an array that is the result of applying <code class="docutils literal"><span class="pre">function</span></code> to each element of <code class="docutils literal"><span class="pre">array</span></code>:</p>
<div class="highlight-sql"><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">&#39;x&#39;</span><span class="p">,</span> <span class="s1">&#39;abc&#39;</span><span class="p">,</span> <span class="s1">&#39;z&#39;</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">&#39;0&#39;</span><span class="p">);</span> <span class="c1">-- [&#39;x0&#39;, &#39;abc0&#39;, &#39;z0&#39;]</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="descname">zip</code><span class="sig-paren">(</span><em>array1, array2[, ...]) -&gt; array(row</em><span class="sig-paren">)</span></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"><span class="pre">NULL</span></code>.</p>
<div class="highlight-sql"><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">&#39;1b&#39;</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="s1">&#39;3b&#39;</span><span class="p">]);</span> <span class="c1">-- [ROW(1, &#39;1b&#39;), ROW(2, null), ROW(null, &#39;3b&#39;)]</span>
</pre></div>
</div>
</dd></dl>

<dl class="function">
<dt id="zip_with">
<code class="descname">zip_with</code><span class="sig-paren">(</span><em>array(T)</em>, <em>array(U)</em>, <em>function(T</em>, <em>U</em>, <em>R)) -&gt; array(R</em><span class="sig-paren">)</span></dt>
<dd><p>Merges the two given arrays, element-wise, into a single array using <code class="docutils literal"><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"><span class="pre">function</span></code>:</p>
<div class="highlight-sql"><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">&#39;a&#39;</span><span class="p">,</span> <span class="s1">&#39;b&#39;</span><span class="p">,</span> <span class="s1">&#39;c&#39;</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(&#39;a&#39;, 1), ROW(&#39;b&#39;, 3), ROW(&#39;c&#39;, 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">&#39;a&#39;</span><span class="p">,</span> <span class="s1">&#39;b&#39;</span><span class="p">,</span> <span class="s1">&#39;c&#39;</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">&#39;d&#39;</span><span class="p">,</span> <span class="s1">&#39;e&#39;</span><span class="p">,</span> <span class="s1">&#39;f&#39;</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">-- [&#39;ad&#39;, &#39;be&#39;, &#39;cf&#39;]</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">&#39;a&#39;</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">&#39;d&#39;</span><span class="p">,</span> <span class="k">null</span><span class="p">,</span> <span class="s1">&#39;f&#39;</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">-- [&#39;a&#39;, null, &#39;f&#39;]</span>
</pre></div>
</div>
</dd></dl>

</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="window.html">7.15. Window Functions</a>
    </span>
    <span class="right">
        <a href="map.html">7.17. Map Functions and Operators</a> &raquo;
    </span>
</p>

</div>

    <div class="footer" role="contentinfo">
    </div>
  </body>
</html>