Array Functions
- reference
You can use array functions to evaluate arrays, perform computations on elements in an array, and to return a new array based on a transformation.
ARRAY_AGG(expr
)
ARRAY_APPEND(expr
, val1
, val2
, …)
Description
This function takes an array expr
and one or more val
arguments to return a new array with the specified val
argument(s) appended.
It requires a minimum of two arguments and returns an error if there are fewer.
Arguments
- expr
-
[Required] The array to be appended to.
- val1, val2, …
-
[At least 1 is required] The text string(s) to be appended.
Return Values
A new array with the specified val
argument(s) appended.
If either of the input argument types are MISSING
, then it returns MISSING
.
If either of the input argument types are NULL
, then it returns NULL
.
If the expr
argument is not an array, then it returns NULL
.
If the expr
is in the WHERE
clause of a partial index, this function lists the expressions that are implicitly covered.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Use ARRAY_APPEND
to add a user to the Public Likes array:
SELECT ARRAY_APPEND(t.public_likes, "Valerie Smith") AS add_user_likes
FROM hotel t
LIMIT 1;
[
{
"add_user_likes": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow",
"Valerie Smith"
]
}
]
ARRAY_AVG(expr
)
Description
This function takes an array expr
as an argument and returns the arithmetic mean (average) of all the non-NULL
number values in the array, or NULL
if there are no such values.
Return Values
A number representing the arithmetic mean (average) of all the non-NULL
number values in the array expression
.
If there are no number values in array expr
, then it returns NULL
.
If the input expr
is MISSING
, then it returns MISSING
.
If the array size of expr
is 0 (no elements), then it returns NULL
.
Any non-number elements in the array expr
are ignored.
ARRAY_BINARY_SEARCH(expr
, val
, …)
Description
This function returns the first position of the specified value val
within the sorted array expr
.
The array position is zero-based, that is, the first position is 0.
This function uses a binary search algorithm. If the array is unsorted, the function may not be able to find the value. |
See also ARRAY_POSITION(), ARRAY_SORT().
Arguments
- expr
-
[Required] The array you want to search, sorted in SQL++ collation order.
- val
-
[Required] The value whose position you want to find.
Return Values
An integer representing the first position of the input val
, where the first position is 0.
If the value val
occurs more than once within the array expr
, only the first position is returned.
It returns -1 if the input val
is not found in the array.
If one of the arguments is MISSING
, it returns MISSING
.
If the input expr
is not an array, it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find which position "Brian Kilback" is in the sorted public_likes
array:
SELECT ARRAY_BINARY_SEARCH(ARRAY_SORT(t.public_likes), "Brian Kilback")
AS sorted_position
FROM hotel t
LIMIT 1;
[
{
"sorted_position": 0
}
]
ARRAY_CONCAT(expr1
, expr2
, …)
Description
This function takes two or more expr
arrays and returns a new array after concatenating the input arrays.
If there are fewer than two arguments, then it returns an error.
Arguments
- expression1, expression2, …
-
[At least 2 are required] The arrays to be concatenated together.
Return Values
A new array, concatenated from the input arrays.
If any of the input expr
arguments or one of the array elements are MISSING
, then it returns MISSING
.
If any of the input expr
arguments is NULL
, then it returns NULL
.
If any of the input expr
arguments is not an array, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Use ARRAY_CONCAT
to add two people to the Public Likes array:
SELECT ARRAY_CONCAT(t.public_likes, ["John McHill", "Dave Smith"]) AS add_user_likes
FROM hotel t
LIMIT 1;
[
{
"add_user_likes": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow",
"John McHill",
"Dave Smith"
]
}
]
ARRAY_CONTAINS(expr
, val
)
Arguments
- expr
-
[Required] The array to be searched.
- val
-
[Required] The value that is being searched for.
Return Values
If either of the input argument types are MISSING
, then it returns MISSING
.
If either of the input argument types are NULL
, then it returns NULL
.
If the expr
argument is not an array, then it returns NULL
.
If the array expr
contains val
, then it returns TRUE
; otherwise, it returns FALSE
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Use ARRAY_CONTAINS
with a Boolean function:
SELECT ARRAY_CONTAINS(t.public_likes, "Vallie Ryan") AS array_contains_value
FROM hotel t
LIMIT 1;
[
{
"array_contains_value": true
}
]
ARRAY_COUNT(expr
)
Return Values
A count of all the non-NULL
values in the array, or zero if there are no such values.
If the expr
argument is MISSING
, then it returns MISSING
.
If the expr
argument is NULL
, then it returns NULL
.
If the expr
argument is not an array, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Use ARRAY_COUNT
to count the total hotel reviews:
SELECT ARRAY_COUNT(t.reviews) AS total_reviews
FROM hotel t
LIMIT 1;
[
{
"total_reviews": 2
}
]
ARRAY_DISTINCT(expr
)
ARRAY_EXCEPT(expr1
, expr2
)
Description
This function returns all the elements of the first array, except for those which are also included in the second array.
Arguments
- expr1
-
[Required] The input array, from which specified elements may be excluded.
- expr2
-
[Required] The array of elements to be excluded.
ARRAY_FLATTEN(expr
, depth
)
Description
This function flattens nested array elements into the top-level array, up to the specified depth.
Arguments
- expr
-
[Required] The multilevel array to be flattened.
- depth
-
[Required] The Integer representing the number of depths to flatten.
Return Value
An array with depth
fewer levels than the input array expr
.
If one of the arguments is MISSING
, it returns MISSING
.
If the input expr
is a non-array, or if the input depth
argument is not an integer, it returns NULL
.
Examples
Create a 3-level array of numbers to flatten by 1 level.
INSERT INTO default (KEY, value)
VALUES ("na", {"a":2, "b":[1,2,[31,32,33],4,[[511, 512], 52]]});
SELECT ARRAY_FLATTEN(b,1) AS flatten_by_1 FROM default USE KEYS ["na"];
[
{
"flatten_by_1": [
1,
2,
31,
32,
33,
4,
[
511,
512
],
52
]
}
]
Flatten the above example by 2 levels.
SELECT ARRAY_FLATTEN(b,2) AS flatten_by_2 FROM default USE KEYS ["na"];
[
{
"flatten_by_2": [
1,
2,
31,
32,
33,
4,
511,
512,
52
]
}
]
ARRAY_IFNULL(expr
)
Description
This function parses the input array expr
and returns the first non-NULL
value in the array.
Return Values
The first non-NULL value in the input array.
If the input expr
is MISSING, then it returns MISSING
.
If the input expr
is a non-array, then it returns NULL
.
Examples
Find the first non-NULL
value in an array of items.
SELECT ARRAY_IFNULL( ["","apples","","bananas","grapes","oranges"]) AS check_null;
[
{
"check_null": ""
}
]
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find the first non-null
hotel reviewers:
SELECT ARRAY_IFNULL(t.public_likes) AS if_null
FROM hotel t
LIMIT 2;
[
{
"if_null": "Julius Tromp I"
},
{
"if_null": null
}
]
ARRAY_INSERT(expr
, pos
, val1
, val2
, …)
Description
This function inserts the specified value
or multiple value
items into the specified position
in the input array expression
, and returns the new array.
Arguments
- expr
-
[Required] The array to insert items into.
- pos
-
[Required] The integer specifying the array position from the left of the input array
expr
, where the 1st position is 0 (zero). - val1, val2, …
-
[At least one is required] The value or multiple value items to insert into the input array expression.
Return Values
An array with the input value or multiple value items inserted into the input array expression at position pos
.
If any of the three arguments are MISSING
, then it returns MISSING
.
If the expr
argument is a non-array or if the position
argument is not an integer, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Insert "jsmith" into the 2nd position of the public_likes
array:
SELECT ARRAY_INSERT(public_likes, 2, "jsmith") AS insert_val
FROM hotel
LIMIT 1;
[
{
"insert_val": [
"Julius Tromp I",
"Corrine Hilll",
"jsmith",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow"
]
}
]
ARRAY_INTERSECT(expr1
, expr2
, ...)
Description
This function takes two or more arrays and returns the intersection of the input arrays as the result; that is, the array containing values that are present in all of the input arrays.
Arguments
- expr1, expr2, …
-
[At least 2 are required] The two or more arrays to compare the values of.
Return Values
An array containing the values that are present in all of the input arrays.
If there are no common elements, then it returns an empty array.
If any of the input arguments are MISSING
, then it returns MISSING
.
If any of the input arguments are non-array values, then it returns NULL
.
Examples
Compare three arrays of fruit for common elements.
SELECT ARRAY_INTERSECT( ["apples","bananas","grapes","orange"], ["apples","orange"], ["apples","grapes"])
AS array_intersection;
[
{
"array_intersection": [
"apples"
]
}
]
Compare three arrays of fruit with no common elements.
SELECT ARRAY_INTERSECT( ["apples","grapes","oranges"], ["apples"],["oranges"],["bananas", "grapes"])
AS array_intersection;
[
{
"array_intersection": []
}
]
ARRAY_LENGTH(expr
)
Equivalent: LEN()
Return Values
An integer representing the number of elements in the input array.
If the input argument is MISSING, then it returns MISSING
.
If the input argument is a non-array value, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find how many total public_likes
there are in the travel-sample
keyspace:
SELECT ARRAY_LENGTH(t.public_likes) AS total_likes
FROM hotel t
LIMIT 1;
[
{
"total_likes": 8
}
]
ARRAY_MAX(expr
)
Description
This function returns the largest non-NULL
, non-MISSING
array element, in SQL++ collation order.
Return Values
The largest non-NULL
, non-MISSING
array element, in SQL++ collation order.
If the input expr
is MISSING
, then it returns MISSING
.
If the input expr
is a non-array value, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find the maximum (last) value of the public_likes
array:
SELECT ARRAY_MAX(t.public_likes) AS max_val
FROM hotel t
LIMIT 1;
[
{
"max_val": "Vallie Ryan"
}
]
ARRAY_MIN(expr
)
Description
This function returns the smallest non-NULL
, non-MISSING
array element, in SQL++ collation order.
Return Values
The smallest non-NULL
, non-MISSING
array element, in SQL++ collation order.
If the input expr
is MISSING
, then it returns MISSING
.
If the input expr
is a non-array value, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find the minimum (first) value of the public_likes
array:
SELECT ARRAY_MIN(t.public_likes) AS min_val
FROM hotel t
LIMIT 1;
[
{
"min_val": "Brian Kilback"
}
]
ARRAY_MOVE(expr
, val1
, val2
)
Description
This function returns a new array containing all the elements of expr
, with one element moved to a new position.
Arguments
- expr
-
[Required] The input array containing an element that you want to move.
- val1
-
[Required] An integer specifying the old location of the element to move.
- val2
-
[Required] An integer specifying the new location of the element to move.
If val1
or val2
are 0 or greater, the position is counted from the left of the input array, where the leftmost position in the array is 0 (zero).
If val1
or val2
are less than 0, the position is counted from the right of the input array, where the rightmost position in the array is -1.
Return Values
An array with the element at the position specified by val1
moved to a new position specified by val2
.
If either of the val
arguments is outside the array, the function returns NULL
.
If any of the arguments is MISSING, then it returns MISSING
.
If the expr
argument is a non-array, or if either of the val
arguments is not an integer, then it returns NULL
.
Examples
Move the first element in the array to the second position in the array.
WITH Letters AS (["a", "b", "c", "d", "e", "f"])
SELECT ARRAY_MOVE(Letters, 0, 1) AS Second;
[
{
"Second": [
"b",
"a",
"c",
"d",
"e",
"f"
]
}
]
Move the first element in an array to the penultimate position in the array.
WITH Letters AS (["a", "b", "c", "d", "e", "f"])
SELECT ARRAY_MOVE(Letters, 0, -2) AS Penultimate;
[
{
"Penultimate": [
"b",
"c",
"d",
"e",
"a",
"f"
]
}
]
ARRAY_POSITION(expr
, val
)
Description
This function returns the first position of the specified value
within the array expression
.
The array position is zero-based, that is, the first position is 0.
See also ARRAY_BINARY_SEARCH().
Arguments
- expr
-
[Required] The array you want to search.
- val
-
[Required] The value whose position you want to know.
Return Values
An integer representing the first position of the input val
, where the first position is 0.
If the value val
occurs more than once within the array expr
, only the first position is returned.
It returns -1 if the input val
does not exist in the array.
If one of the arguments is MISSING
, it returns MISSING
.
If either of the arguments are non-array values, it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Find which position "Brian Kilback" is in the public_likes
array:
SELECT ARRAY_POSITION(t.public_likes, "Brian Kilback") AS array_position
FROM hotel t
LIMIT 1;
[
{
"array_position": 4
}
]
ARRAY_PREPEND(val1
, val2
, … , expr
)
Description
This function returns the new array after prepending the array expr
with the specified val
or multiple val
arguments.
It requires a minimum of two arguments.
Arguments
- val1, val2, …
-
[At least 1 is required] The value or multiple value arguments to prepend to the input
expr
. - expression
-
[Required] The array you want to have the input
value
argument(s) prepended to.
Return Values
A new array with the input val
argument(s) prepended to the input array expr
.
If one of the arguments is MISSING
, it returns MISSING
.
If the last argument is a non-array, it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Prepend "Dave Smith" to the front of the public_likes
array:
SELECT ARRAY_PREPEND("Dave Smith",t.public_likes) AS prepend_val
FROM hotel t
LIMIT 1;
[
{
"prepend_val": [
"Dave Smith",
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow"
]
}
]
ARRAY_PUT(expr
, val1
, val2
, …)
Description
This function returns a new array with val
or multiple val
arguments appended if the val
is not already present.
Otherwise, it returns the unmodified input array expr
.
It requires a minimum of two arguments.
Arguments
- expr
-
[Required] The array you want to append the input
value
orvalue
arguments. - val1, val2, …
-
[At least 1 is required] The value or multiple value arguments that you want appended to the end of the input array
expression
.
Return Values
A new array with val
or multiple val
arguments appended if the val
is not already present.
Otherwise, it returns the unmodified input array expr
.
If one of the arguments is MISSING
, then it returns MISSING
.
If the first argument is a non-array, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Append "Dave Smith" to the end of the public_likes
array:
SELECT ARRAY_PUT(t.public_likes, "Dave Smith") AS array_put
FROM hotel t
LIMIT 1;
[
{
"array_put": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow",
"Dave Smith"
]
}
]
ARRAY_RANGE(start_num
, end_num
step_num
])
Description
This function returns a new array of numbers, from start_num
until the largest number less than end_num
.
Successive numbers are incremented by step_int
.
If step_int
is not specified, then the default value is 1.
If step_num
is negative, then he function decrements until the smallest number greater than end_num
.
Arguments
- start_num
-
[Required] The integer to start a new array with.
- end_num
-
[Required] The integer that is one number larger than the final integer in the output array.
- step_num
-
[Optional; default is 1] The number between each array element.
If step_num
is negative, then the function decrements until the smallest number greater than end_num
.
- Output Values
-
A new array of numbers, from
start_num
until the largest number less thanend_num
.
If any of the arguments are MISSING
, then it returns MISSING
.
If any of the arguments do not start with a digit, then it returns an error.
Examples
Make an array from 0 to 20 by stepping every 5th number.
SELECT ARRAY_RANGE(0, 25, 5) AS gen_array_range_5;
[
{
"gen_array_range_5": [
0,
5,
10,
15,
20
]
}
]
Make an array from 0.1 to 1.1 by stepping every 2nd number.
SELECT ARRAY_RANGE(0.1, 2) AS gen_array_range_2;
[
{
"gen_array_range_2": [
0.1,
1.1
]
}
]
Make an array from 10 to 3 by stepping down every 3rd number.
SELECT ARRAY_RANGE(10, 3, -3) AS gen_array_range_minus3;
[
{
"gen_array_range-3": [
10,
7,
4
]
}
]
ARRAY_REMOVE(expr
, val1
, val2
, …)
Description
This function returns a new array with all occurrences of the specified value
or multiple value
fields removed from the array expression
.
It requires a minimum of two arguments.
Arguments
- expr
-
[Required] The input array to have the specified
val
or multipleval
fields removed. - val1, val2, …
-
[At least 1 is required] The input value or multiple values to remove from the input array
expr
. - Output Values
-
A new array with all occurrences of the specified
val
or multipleval
fields removed from the arrayexpr
.
If any of the arguments are MISSING
, then it returns MISSING
.
If the first argument is not an array, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Remove "Vallie Ryan" from the public_likes
array:
SELECT ARRAY_REMOVE(t.public_likes, "Vallie Ryan") AS remove_val
FROM hotel t
LIMIT 1;
[
{
"remove_val": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow"
]
}
]
ARRAY_REPEAT(val
, rep_int
)
Arguments
- val
-
[Required] The input value you want repeated.
- rep_int
-
[Required] The integer number of times you want the input
val
repeated. - Output Values
-
A new array with the specified
val
repeatedrep_int
times.
If any of the arguments are MISSING
, then it returns MISSING
.
If the rep_int
argument is not an integer, then it returns NULL
.
ARRAY_REPLACE(expr
, val1
, val2
max_int
])
Description
This function returns a new array with all occurrences of value1
replaced with value2
.
If max_int
is specified, than no more than max_int
replacements will be performed.
Arguments
- expr
-
[Required] The input array you want to replace
val1
withval2
. - val1
-
[Required] The existing value in the input
expr
you want to replace. - val2
-
[Required] The new value you want to take the place of
val1
in the inputexpr
. - max_int
-
[Optional. Default is no maximum] The number of maximum replacements to perform.
Return Values
A new array with all or max_int
occurrences of val1
replaced with val2
.
If any of the arguments are MISSING
, then it returns MISSING
.
If the first argument is not an array or if the second argument is NULL
, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Replace all occurrences of "Vallie Ryan" with "Valerie Ryan":
SELECT ARRAY_REPLACE(t.public_likes, "Vallie Ryan", "Valerie Ryan") AS replace_val
FROM hotel t
LIMIT 1;
[
{
"replace_val": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Valerie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow"
]
}
]
ARRAY_REVERSE(expr
)
Return Values
A new array with all the elements of expr
in reverse order.
If the argument is MISSING
, then it returns MISSING
.
If the argument is a non-array value, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Reverse the values in the public_likes
array:
SELECT ARRAY_REVERSE(t.public_likes) AS reverse_val
FROM hotel t
LIMIT 1;
[
{
"reverse_val": [
"Elnora Trantow",
"Ms. Moses Feeney",
"Lilian McLaughlin",
"Brian Kilback",
"Vallie Ryan",
"Jaeden McKenzie",
"Corrine Hilll",
"Julius Tromp I"
]
}
]
ARRAY_SORT(expr
)
Description
This function returns a new array with the elements of expr
sorted in SQL++ collation order.
Return Values
A new array with the elements of expr
sorted in SQL++ collation order.
If the argument is MISSING
, then it returns MISSING
.
If the argument is a non-array value, then it returns NULL
.
Example
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Sort the public_likes
array:
SELECT ARRAY_SORT(t.public_likes) AS sorted_array
FROM hotel t
LIMIT 1;
[
{
"sorted_array": [
"Brian Kilback",
"Corrine Hilll",
"Elnora Trantow",
"Jaeden McKenzie",
"Julius Tromp I",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Vallie Ryan"
]
}
]
ARRAY_STAR(expr
)
Output Values
An object of arrays.
If the argument is MISSING
, then it returns MISSING
.
If the argument is a non-array value, then it returns NULL
.
Example
Convert a given array of two documents each with five items into an object of five arrays each with two documents.
SELECT ARRAY_STAR( [
{
"address": "Capstone Road, ME7 3JE",
"city": "Medway",
"country": "United Kingdom",
"name": "Medway Youth Hostel",
"url": "http://www.yha.org.uk"
},
{
"address": "6 rue aux Juifs",
"city": "Giverny",
"country": "France",
"name": "The Robins",
"url": "http://givernyguesthouse.com/robin.htm"
}]) AS array_star;
[
{
"array_star": {
"address": [
"Capstone Road, ME7 3JE",
"6 rue aux Juifs"
],
"city": [
"Medway",
"Giverny"
],
"country": [
"United Kingdom",
"France"
],
"name": [
"Medway Youth Hostel",
"The Robins"
],
"url": [
"http://www.yha.org.uk",
"http://givernyguesthouse.com/robin.htm"
]
}
}
]
Array References
You can use an asterisk (*) as an array subscript which converts the array to an object of arrays. The following example returns an array of the ages of the given contact’s children:
SELECT children[*].age FROM contacts WHERE fname = "Dave"
An equivalent query can be written using the array_star()
function:
SELECT array_star(children).age FROM contacts WHERE fname = "Dave"
ARRAY_SUM(expr
)
ARRAY_SYMDIFF(expr1
, expr2
, …)
This function has a synonym ARRAY_SYMDIFF1().
Description
This function returns a new array based on the set symmetric difference, or disjunctive union, of the input expression
arrays.
The new array contains only those elements that appear in exactly one of the input arrays, and it requires a minimum of two arguments.
Return Values
A new array containing only those elements that appear in exactly one of the input arrays.
If any of the arguments is MISSING
, then it returns MISSING
.
If any of the arguments is a non-array value, then it returns NULL
.
The difference between ARRAY_SYMDIFF() and ARRAY_SYMDIFFN() is that the former function includes the value when it appears only once, while the latter function includes the value when it appears an odd number of times in the input arrays. Refer to the following article for more information on the difference between a normal and n-ary symdiff: https://en.wikipedia.org/wiki/Symmetric_difference. |
ARRAY_SYMDIFF1(expr1
, expr2
, …)
Synonym of ARRAY_SYMDIFF().
ARRAY_SYMDIFFN(expr1
, expr2
, …)
Description
This function returns a new array based on the set symmetric difference, or disjunctive union, of the input arrays. The new array contains only those elements that appear in an odd number of input arrays, and it requires a minimum of two arguments.
Return Values
A new array containing only those elements that appear in an odd number of the input arrays.
If any of the arguments is MISSING
, then it returns MISSING
.
If any of the arguments is a non-array value, then it returns NULL
.
The difference between ARRAY_SYMDIFF() and ARRAY_SYMDIFFN() is that the former function includes the value when it appears only once, while the latter function includes the value when it appears an odd number of times in the input arrays. Refer to the following article for more information on the difference between a normal and n-ary symdiff: https://en.wikipedia.org/wiki/Symmetric_difference. |
ARRAY_UNION(expr1
, expr2
, …)
Description
This function returns a new array with the set union of the input arrays, and it requires a minimum of two arguments.
Return Values
A new array with the set union of the input arrays.
If any of the arguments is MISSING
, then it returns MISSING
.
If any of the arguments is a non-array value, then it returns NULL
.
Examples
List the union of three given arrays.
SELECT ARRAY_UNION([1, 2], [1, 2, 4], [1, 3]) AS array_union;
[
{
"array_union": [
3,
2,
1,
4
]
}
]
List the union of two given arrays with a string.
SELECT ARRAY_UNION([1, 2], [1, 2, 4], "abc") AS array_union;
[
{
"array_union": null
}
]