Array functions
These functions can be used when working with, and manipulating arrays of data.
Function | Description |
---|---|
array::combine()
|
Combines all values from two arrays together |
array::concat()
|
Returns the merged values from two arrays |
array::difference()
|
Returns the difference between two arrays |
array::distinct()
|
Returns the unique items in an array |
array::intersect()
|
Returns the values which intersect two arrays |
array::len()
|
Returns the length of an array |
array::sort()
|
Sorts the values in an array in ascending or descending order |
array::sort::asc()
|
Sorts the values in an array in ascending order |
array::sort::desc()
|
Sorts the values in an array in descending order |
array::union()
|
Returns the unique merged values from two arrays |
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