array::combine

The array::combine function combines all values from two arrays together, returning an array of arrays.

array::combine(array, array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::combine([1,2], [2,3]);

[ [1,2], [1,3], [2,3] ]

If either of the two arguments do not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::combine(3, true);

null

array::concat

The array::concat function merges two arrays together, returning an array which may contain duplicate values. If you want to remove duplicate values from the resulting array, then use the array::union() function

array::concat(array, array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::concat([1,2,3,4], [3,4,5,6]);

[1,2,3,4,3,4,5,6]

If either of the two arguments do not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::concat(3, true);

null

array::difference

The array::difference determines the difference between two arrays, returning a single array containing items which are not in both arrays.

array::difference(array, array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::difference([1,2,3,4], [3,4,5,6]);

[1,2,5,6]

If either of the two arguments do not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::difference(3, true);

null

array::distinct

The array::distinct function calculates the unique values in an array, returning a single array.

array::distinct(array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::distinct([1,2,1,3,3,4]);

[1,2,3,4]

If the argument does not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::distinct("some text");

null

array::intersect

The array::intersect function calculates the values which intersect two arrays, returning a single array containing the values which are in both arrays.

array::intersect(array, array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::intersect([1,2,3,4], [3,4,5,6]);

[3,4]

If either of the two arguments do not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::difference(3, true);

null

array::len

The array::len function calculates the length of an array, returning a number. This function includes all items when counting the number of items in the array. If you want to only count truthy values, then use the count() function.

array::len(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::len([1,2,1,null,"something",3,3,4,0]);

9

If the argument does not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::len("some text");

null

array::sort

The array::sort function calculates the length of an array, returning a number. This function includes all items when counting the number of items in the array. If you want to only count truthy values, then use the count() function.

array::sort(array) -> array

The function also accepts a second boolean parameter which determines the sorting direction. The second parameter can be true for ascending order, or false for descending order.

count(array, bool) -> array

The function also accepts a second string parameter which determines the sorting direction. The second parameter can be 'asc' for ascending order, or 'desc' for descending order.

count(array, string) -> array

The following examples show this function, and its output, when used in a select statement:

SELECT * FROM array::sort([1,2,1,null,"something",3,3,4,0]);

[null,0,1,1,2,3,3,4,"something"]

SELECT * FROM array::sort([1,2,1,null,"something",3,3,4,0], false);

["something",4,3,3,2,1,1,9,null]

SELECT * FROM array::sort([1,2,1,null,"something",3,3,4,0], "asc");

[null,0,1,1,2,3,3,4,"something"]

SELECT * FROM array::sort([1,2,1,null,"something",3,3,4,0], "desc");

["something",4,3,3,2,1,1,9,null]

If the first argument does not evaluate to an array, then the value will be returned without modification:

SELECT * FROM array::sort("some text");

"some text"

array::sort::asc

The array::sort::asc function is a shorthand convenience function for the array::sort function, to sort values in an array in ascending order.

array::sort::asc(array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::sort::asc([1,2,1,null,"something",3,3,4,0]);

[null,0,1,1,2,3,3,4,"something"]

If the argument does not evaluate to an array, then the value will be returned without modification:

SELECT * FROM array::sort::asc("some text");

"some text"

array::sort::desc

The array::sort::desc function is a shorthand convenience function for the array::sort function, to sort values in an array in descending order.

array::sort::desc(array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::sort::desc([1,2,1,null,"something",3,3,4,0]);

["something",4,3,3,2,1,1,9,null]

If the argument does not evaluate to an array, then the value will be returned without modification:

SELECT * FROM array::sort::desc("some text");

"some text"

array::union

The array::union function combines two arrays together, removing duplicate values, and returning a single array.

array::union(array, array) -> array

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM array::union([1,2,1,6], [1,3,4,5,6]);

[1,2,6,3,4,5]

If either of the two arguments do not evaluate to an array, then an EMPTY value will be returned:

SELECT * FROM array::union(3, true);

null