This section provides reference information for the array functions in PlaidCloud Lakehouse.
This is the multi-page printable view of this section. Click here to print.
Array Functions
- 1: ARRAY_AGGREGATE
- 2: ARRAY_APPEND
- 3: ARRAY_APPLY
- 4: ARRAY_CONCAT
- 5: ARRAY_CONTAINS
- 6: ARRAY_DISTINCT
- 7: ARRAY_FILTER
- 8: ARRAY_FLATTEN
- 9: ARRAY_GET
- 10: ARRAY_INDEXOF
- 11: ARRAY_LENGTH
- 12: ARRAY_PREPEND
- 13: ARRAY_REDUCE
- 14: ARRAY_REMOVE_FIRST
- 15: ARRAY_REMOVE_LAST
- 16: ARRAY_SLICE
- 17: ARRAY_SORT
- 18: ARRAY_TO_STRING
- 19: ARRAY_TRANSFORM
- 20: ARRAY_UNIQUE
- 21: CONTAINS
- 22: GET
- 23: RANGE
- 24: SLICE
- 25: UNNEST
1 - ARRAY_AGGREGATE
Aggregates elements in the array with an aggregate function.
Analyze Syntax
func.array_aggregate( <array>, '<agg_func>' )
Supported aggregate functions include
avg,count,max,min,sum,any,stddev_samp,stddev_pop,stddev,std,median,approx_count_distinct,kurtosis, andskewness.The syntax can be rewritten as
func.array_<agg_func>( <array> ). For example,func.array_avg( <array> ).
Analyze Examples
func.array_aggregate([1, 2, 3, 4], 'sum'), func.array_sum([1, 2, 3, 4])
┌──────────────────────────────────────────────────────────────────────────┐
│ func.array_aggregate([1, 2, 3, 4], 'sum') │ func.array_sum([1, 2, 3, 4])│
├────────────────────────────────────────────┼─────────────────────────────┤
│ 10 │ 10 │
└──────────────────────────────────────────────────────────────────────────┘
SQL Syntax
ARRAY_AGGREGATE( <array>, '<agg_func>' )
Supported aggregate functions include
avg,count,max,min,sum,any,stddev_samp,stddev_pop,stddev,std,median,approx_count_distinct,kurtosis, andskewness.The syntax can be rewritten as
ARRAY_<agg_func>( <array> ). For example,ARRAY_AVG( <array> ).
SQL Examples
SELECT ARRAY_AGGREGATE([1, 2, 3, 4], 'SUM'), ARRAY_SUM([1, 2, 3, 4]);
┌────────────────────────────────────────────────────────────────┐
│ array_aggregate([1, 2, 3, 4], 'sum') │ array_sum([1, 2, 3, 4]) │
├──────────────────────────────────────┼─────────────────────────┤
│ 10 │ 10 │
└────────────────────────────────────────────────────────────────┘
2 - ARRAY_APPEND
Prepends an element to the array.
Analyze Syntax
func.array_append( <array>, <element>)
Analyze Examples
func.array_append([3, 4], 5)
┌──────────────────────────────┐
│ func.array_append([3, 4], 5) │
├──────────────────────────────┤
│ [3,4,5] │
└──────────────────────────────┘
SQL Syntax
ARRAY_APPEND( <array>, <element>)
SQL Examples
SELECT ARRAY_APPEND([3, 4], 5);
┌─────────────────────────┐
│ array_append([3, 4], 5) │
├─────────────────────────┤
│ [3,4,5] │
└─────────────────────────┘
3 - ARRAY_APPLY
Alias for ARRAY_TRANSFORM.
4 - ARRAY_CONCAT
Concats two arrays.
Analyze Syntax
func.array_concat( <array1>, <array2> )
Analyze Examples
func.array_concat([1, 2], [3, 4])
┌────────────────────────────────────┐
│ func.array_concat([1, 2], [3, 4]) │
├────────────────────────────────────┤
│ [1,2,3,4] │
└────────────────────────────────────┘
SQL Syntax
ARRAY_CONCAT( <array1>, <array2> )
SQL Examples
SELECT ARRAY_CONCAT([1, 2], [3, 4]);
┌──────────────────────────────┐
│ array_concat([1, 2], [3, 4]) │
├──────────────────────────────┤
│ [1,2,3,4] │
└──────────────────────────────┘
5 - ARRAY_CONTAINS
Alias for CONTAINS.
6 - ARRAY_DISTINCT
Removes all duplicates and NULLs from the array without preserving the original order.
Analyze Syntax
func.array_distinct( <array> )
Analyze Examples
func.array_distinct([1, 2, 2, 4, 3])
┌───────────────────────────────────────┐
│ func.array_distinct([1, 2, 2, 4, 3]) │
├───────────────────────────────────────┤
│ [1,2,4,3] │
└───────────────────────────────────────┘
SQL Syntax
ARRAY_DISTINCT( <array> )
SQL Examples
SELECT ARRAY_DISTINCT([1, 2, 2, 4, 3]);
┌─────────────────────────────────┐
│ array_distinct([1, 2, 2, 4, 3]) │
├─────────────────────────────────┤
│ [1,2,4,3] │
└─────────────────────────────────┘
7 - ARRAY_FILTER
Constructs an array from those elements of the input array for which the lambda function returns true.
Analyze Syntax
func.array_filter( <array>, <lambda> )
Analyze Examples
func.array_filter([1, 2, 3], x -> (x > 1))
┌─────────────────────────────────────────────┐
│ func.array_filter([1, 2, 3], x -> (x > 1)) │
├─────────────────────────────────────────────┤
│ [2,3] │
└─────────────────────────────────────────────┘
SQL Syntax
ARRAY_FILTER( <array>, <lambda> )
SQL Examples
SELECT ARRAY_FILTER([1, 2, 3], x -> x > 1);
┌───────────────────────────────────────┐
│ array_filter([1, 2, 3], x -> (x > 1)) │
├───────────────────────────────────────┤
│ [2,3] │
└───────────────────────────────────────┘
8 - ARRAY_FLATTEN
Flattens nested arrays, converting them into a single-level array.
Analyze Syntax
func.array_flatten( <array> )
Analyze Examples
func.array_flatten([[1, 2], [3, 4, 5]])
┌──────────────────────────────────────────┐
│ func.array_flatten([[1, 2], [3, 4, 5]]) │
├──────────────────────────────────────────┤
│ [1,2,3,4,5] │
└──────────────────────────────────────────┘
SQL Syntax
ARRAY_FLATTEN( <array> )
SQL Examples
SELECT ARRAY_FLATTEN([[1,2], [3,4,5]]);
┌────────────────────────────────────┐
│ array_flatten([[1, 2], [3, 4, 5]]) │
├────────────────────────────────────┤
│ [1,2,3,4,5] │
└────────────────────────────────────┘
9 - ARRAY_GET
Alias for GET.
10 - ARRAY_INDEXOF
Returns the index(1-based) of an element if the array contains the element.
Analyze Syntax
func.array_indexof( <array>, <element> )
Analyze Examples
func.array_indexof([1, 2, 9], 9)
┌───────────────────────────────────┐
│ func.array_indexof([1, 2, 9], 9) │
├───────────────────────────────────┤
│ 3 │
└───────────────────────────────────┘
SQL Syntax
ARRAY_INDEXOF( <array>, <element> )
SQL Examples
SELECT ARRAY_INDEXOF([1, 2, 9], 9);
┌─────────────────────────────┐
│ array_indexof([1, 2, 9], 9) │
├─────────────────────────────┤
│ 3 │
└─────────────────────────────┘
11 - ARRAY_LENGTH
Returns the length of an array.
Analyze Syntax
func.array_length( <array> )
Analyze Examples
func.array_length([1, 2])
┌────────────────────────────┐
│ func.array_length([1, 2]) │
├────────────────────────────┤
│ 2 │
└────────────────────────────┘
SQL Syntax
ARRAY_LENGTH( <array> )
SQL Examples
SELECT ARRAY_LENGTH([1, 2]);
┌──────────────────────┐
│ array_length([1, 2]) │
├──────────────────────┤
│ 2 │
└──────────────────────┘
12 - ARRAY_PREPEND
Prepends an element to the array.
Analyze Syntax
func.array_prepend( <element>, <array> )
Analyze Examples
func.array_prepend(1, [3, 4])
┌────────────────────────────────┐
│ func.array_prepend(1, [3, 4]) │
├────────────────────────────────┤
│ [1,3,4] │
└────────────────────────────────┘
SQL Syntax
ARRAY_PREPEND( <element>, <array> )
SQL Examples
SELECT ARRAY_PREPEND(1, [3, 4]);
┌──────────────────────────┐
│ array_prepend(1, [3, 4]) │
├──────────────────────────┤
│ [1,3,4] │
└──────────────────────────┘
13 - ARRAY_REDUCE
Applies iteratively the lambda function to the elements of the array, so as to reduce the array to a single value.
Analyze Syntax
func.array_reduce( <array>, <lambda> )
Analyze Examples
func.array_reduce([1, 2, 3, 4], (x, y) -> (x + y))
┌─────────────────────────────────────────────────────┐
│ func.array_reduce([1, 2, 3, 4], (x, y) -> (x + y)) │
├─────────────────────────────────────────────────────┤
│ 10 │
└─────────────────────────────────────────────────────┘
SQL Syntax
ARRAY_REDUCE( <array>, <lambda> )
SQL Examples
SELECT ARRAY_REDUCE([1, 2, 3, 4], (x,y) -> x + y);
┌───────────────────────────────────────────────┐
│ array_reduce([1, 2, 3, 4], (x, y) -> (x + y)) │
├───────────────────────────────────────────────┤
│ 10 │
└───────────────────────────────────────────────┘
14 - ARRAY_REMOVE_FIRST
Removes the first element from the array.
Analyze Syntax
func.array_remove_first( <array> )
Analyze Examples
func.array_remove_first([1, 2, 3])
┌─────────────────────────────────────┐
│ func.array_remove_first([1, 2, 3]) │
├─────────────────────────────────────┤
│ [2,3] │
└─────────────────────────────────────┘
SQL Syntax
ARRAY_REMOVE_FIRST( <array> )
SQL Examples
SELECT ARRAY_REMOVE_FIRST([1, 2, 3]);
┌───────────────────────────────┐
│ array_remove_first([1, 2, 3]) │
├───────────────────────────────┤
│ [2,3] │
└───────────────────────────────┘
15 - ARRAY_REMOVE_LAST
Removes the last element from the array.
Analyze Syntax
func.array_remove_last( <array> )
Analyze Examples
func.array_remove_last([1, 2, 3])
┌────────────────────────────────────┐
│ func.array_remove_last([1, 2, 3]) │
├────────────────────────────────────┤
│ [1,2] │
└────────────────────────────────────┘
SQL Syntax
ARRAY_REMOVE_LAST( <array> )
SQL Examples
SELECT ARRAY_REMOVE_LAST([1, 2, 3]);
┌──────────────────────────────┐
│ array_remove_last([1, 2, 3]) │
├──────────────────────────────┤
│ [1,2] │
└──────────────────────────────┘
16 - ARRAY_SLICE
Alias for SLICE.
17 - ARRAY_SORT
Sorts elements in the array in ascending order.
Analyze Syntax
func.array_sort( <array>[, <order>, <nullposition>] )
| Parameter | Default | Description |
|---|---|---|
| order | ASC | Specifies the sorting order as either ascending (ASC) or descending (DESC). |
| nullposition | NULLS FIRST | Determines the position of NULL values in the sorting result, at the beginning (NULLS FIRST) or at the end (NULLS LAST) of the sorting output. |
Analyze Examples
func.array_sort([1, 4, 3, 2])
┌────────────────────────────────┐
│ func.array_sort([1, 4, 3, 2]) │
├────────────────────────────────┤
│ [1,2,3,4] │
└────────────────────────────────┘
SQL Syntax
ARRAY_SORT( <array>[, <order>, <nullposition>] )
| Parameter | Default | Description |
|---|---|---|
| order | ASC | Specifies the sorting order as either ascending (ASC) or descending (DESC). |
| nullposition | NULLS FIRST | Determines the position of NULL values in the sorting result, at the beginning (NULLS FIRST) or at the end (NULLS LAST) of the sorting output. |
SQL Examples
SELECT ARRAY_SORT([1, 4, 3, 2]);
┌──────────────────────────┐
│ array_sort([1, 4, 3, 2]) │
├──────────────────────────┤
│ [1,2,3,4] │
└──────────────────────────┘
18 - ARRAY_TO_STRING
Concatenates elements of an array into a single string, using a specified separator.
Analyze Syntax
func.array_to_string( <array>, '<separator>' )
Analyze Examples
func.array_to_string(['apple', 'banana', 'cherry'], ', ')
┌────────────────────────────────────────────────────────────┐
│ func.array_to_string(['apple', 'banana', 'cherry'], ', ') │
├────────────────────────────────────────────────────────────┤
│ Apple, Banana, Cherry │
└────────────────────────────────────────────────────────────┘
SQL Syntax
ARRAY_TO_STRING( <array>, '<separator>' )
SQL Examples
SELECT ARRAY_TO_STRING(['Apple', 'Banana', 'Cherry'], ', ');
┌──────────────────────────────────────────────────────┐
│ array_to_string(['apple', 'banana', 'cherry'], ', ') │
├──────────────────────────────────────────────────────┤
│ Apple, Banana, Cherry │
└──────────────────────────────────────────────────────┘
19 - ARRAY_TRANSFORM
Returns an array that is the result of applying the lambda function to each element of the input array.
Analyze Syntax
func.array_transform( <array>, <lambda> )
Analyze Examples
func.array_transform([1, 2, 3], x -> (x + 1))
┌───────────────────────────────────────────────┐
│ func.array_transform([1, 2, 3], x -> (x + 1)) │
├───────────────────────────────────────────────┤
│ [2,3,4] │
└───────────────────────────────────────────────┘
SQL Syntax
ARRAY_TRANSFORM( <array>, <lambda> )
Aliases
SQL Examples
SELECT ARRAY_TRANSFORM([1, 2, 3], x -> x + 1);
┌──────────────────────────────────────────┐
│ array_transform([1, 2, 3], x -> (x + 1)) │
├──────────────────────────────────────────┤
│ [2,3,4] │
└──────────────────────────────────────────┘
20 - ARRAY_UNIQUE
Counts unique elements in the array (except NULL).
Analyze Syntax
func.array_unique( <array> )
Analyze Examples
func.array_unique([1, 2, 3, 3, 4])
┌─────────────────────────────────────┐
│ func.array_unique([1, 2, 3, 3, 4]) │
├─────────────────────────────────────┤
│ 4 │
└─────────────────────────────────────┘
SQL Syntax
ARRAY_UNIQUE( <array> )
SQL Examples
SELECT ARRAY_UNIQUE([1, 2, 3, 3, 4]);
┌───────────────────────────────┐
│ array_unique([1, 2, 3, 3, 4]) │
├───────────────────────────────┤
│ 4 │
└───────────────────────────────┘
21 - CONTAINS
Checks if the array contains a specific element.
Analyze Syntax
func.contains( <array>, <element> )
Analyze Examples
func.contains([1, 2], 1)
┌───────────────────────────┐
│ func.contains([1, 2], 1) │
├───────────────────────────┤
│ true │
└───────────────────────────┘
SQL Syntax
CONTAINS( <array>, <element> )
Aliases
SQL Examples
SELECT ARRAY_CONTAINS([1, 2], 1), CONTAINS([1, 2], 1);
┌─────────────────────────────────────────────────┐
│ array_contains([1, 2], 1) │ contains([1, 2], 1) │
├───────────────────────────┼─────────────────────┤
│ true │ true │
└─────────────────────────────────────────────────┘
22 - GET
Returns an element from an array by index (1-based).
Analyze Syntax
func.get( <array>, <index> )
Analyze Examples
func.get([1, 2], 2)
┌─────────────────────┐
│ func.get([1, 2], 2) │
├─────────────────────┤
│ 2 │
└─────────────────────┘
SQL Syntax
GET( <array>, <index> )
Aliases
SQL Examples
SELECT GET([1, 2], 2), ARRAY_GET([1, 2], 2);
┌───────────────────────────────────────┐
│ get([1, 2], 2) │ array_get([1, 2], 2) │
├────────────────┼──────────────────────┤
│ 2 │ 2 │
└───────────────────────────────────────┘
23 - RANGE
Returns an array collected by [start, end).
Analyze Syntax
func.range( <start>, <end> )
SQAnalyzeL Examples
func.range(1, 5)
┌────────────────────┐
│ func.range(1, 5) │
├────────────────────┤
│ [1,2,3,4] │
└────────────────────┘
SQL Syntax
RANGE( <start>, <end> )
SQL Examples
SELECT RANGE(1, 5);
┌───────────────┐
│ range(1, 5) │
├───────────────┤
│ [1,2,3,4] │
└───────────────┘
24 - SLICE
Extracts a slice from the array by index (1-based).
Analyze Syntax
func.slice( <array>, <start>[, <end>] )
Analyze Examples
func.slice([1, 21, 32, 4], 2, 3)
┌──────────────────────────────────┐
│ func.slice([1, 21, 32, 4], 2, 3) │
├──────────────────────────────────┤
│ [21,32] │
└──────────────────────────────────┘
SQL Syntax
SLICE( <array>, <start>[, <end>] )
Aliases
SQL Examples
SELECT ARRAY_SLICE([1, 21, 32, 4], 2, 3), SLICE([1, 21, 32, 4], 2, 3);
┌─────────────────────────────────────────────────────────────────┐
│ array_slice([1, 21, 32, 4], 2, 3) │ slice([1, 21, 32, 4], 2, 3) │
├───────────────────────────────────┼─────────────────────────────┤
│ [21,32] │ [21,32] │
└─────────────────────────────────────────────────────────────────┘
25 - UNNEST
Unnests the array and returns the set of elements.
Analyze Syntax
func.unnest( <array> )
Analyze Examples
func.unnest([1, 2])
┌──────────────────────┐
│ func.unnest([1, 2]) │
├──────────────────────┤
│ 1 │
│ 2 │
└──────────────────────┘
SQL Syntax
UNNEST( <array> )
SQL Examples
SELECT UNNEST([1, 2]);
┌─────────────────┐
│ unnest([1, 2]) │
├─────────────────┤
│ 1 │
│ 2 │
└─────────────────┘
-- UNNEST(array) can be used as a table function.
SELECT * FROM UNNEST([1, 2]);
┌─────────────────┐
│ value │
├─────────────────┤
│ 1 │
│ 2 │
└─────────────────┘
A Practical Example
In the examples below, we will use the following table called contacts with the phones column defined with an array of text.
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name VARCHAR (100),
phones TEXT []
);
The phones column is a one-dimensional array that holds various phone numbers that a contact may have.
To define multiple dimensional array, you add the square brackets.
For example, you can define a two-dimensional array as follows:
column_name data_type [][]
An example of inserting data into that table
INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);
or
INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
('William Gate','{"(408)-589-5842","(408)-589-5843"}');
The unnest() function expands an array to a list of rows. For example, the following query expands all phone numbers of the phones array.
SELECT
name,
unnest(phones)
FROM
contacts;
Output:
| name | unnest |
|---|---|
| John Doe | (408)-589-5846 |
| John Doe | (408)-589-5555 |
| Lily Bush | (408)-589-5841 |
| William Gate | (408)-589-5843 |