Array Functions
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
)
- Description
-
This function returns an array of the non-
MISSING
group values in the inputexpr
, includingNULL
values. - Arguments
-
- expr
-
[Required] The group of elements you wish to output in an array.
- Return Values
-
An array of non-MISSING values.
If the input
expression
isMISSING
or if one of the elements in the array isMISSING
, then it returnsMISSING
.
Example 8: Use ARRAY_AGG
to group a list of three items into an array.
SELECT ARRAY_AGG(["abc",1,NULL]) AS array_aggregate; Results: [ { "array_aggregate": [ [ "abc", 1, null ] ] } ]
ARRAY_APPEND(expr
, val1
, val2
, …)
- Description
-
This function takes an array
expr
and one or moreval
arguments to return a new array with the specifiedval
argument(s) appended. - 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.It requires a minimum of two arguments and returns an error if there are fewer.
If either of the input argument types are
MISSING
, then it returnsMISSING
.If either of the input argument types are
NULL
, then it returnsNULL
.If the
expr
argument is not an array, then it returnsNULL
.If the
expr
is in theWHERE
clause of a partial index, this function lists the expressions that are implicitly covered.
Example 1: 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 `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "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, orNULL
if there are no such values. - Arguments
-
- expr
-
[Required] The array of numbers to be evaluated.
- Return Values
-
A number representing the arithmetic mean (average) of all the non-
NULL
number values in the arrayexpression
.If there are no number values in array
expr
, then it returnsNULL
.If the input
expr
isMISSING
, then it returnsMISSING
.If the array size of
expr
is 0 (no elements), then it returnsNULL
.Any non-number elements in the array
expr
are ignored.
Example 2: Use ARRAY_AVG
with a set of numbers.
SELECT ARRAY_AVG([0,1,1,2,3,5]) AS array_average; Results: [ { "array_average": 2 } ]
ARRAY_CONCAT(expr1
, expr2
, …)
- Description
-
This function takes two or more
expr
arrays and returns a new array after concatenating the input arrays. - Arguments
-
- expression1, expression2, …
-
[At least 2 are Required] The arrays to be concatenated together.
- Return Values
-
If there are fewer than two arguments, then it returns an error.
If any of the input
expr
arguments or one of the array elements areMISSING
, then it returnsMISSING
.If any of the input
expr
arguments isNULL
, then it returnsNULL
.If any of the input
expr
arguments is not an array, then it returnsNULL
.
Example 3: 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 `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "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
)
- Description
-
This functions checks if the array
expression
contains the specifiedvalue
. - 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 returnsMISSING
.If either of the input argument types are
NULL
, then it returnsNULL
.If the
expr
argument is not an array, then it returnsNULL
.If the array
expr
containsval
, then it returnsTRUE
; otherwise, it returnsFALSE
.
Example 4: Use ARRAY_CONTAINS
with a Boolean function.
SELECT ARRAY_CONTAINS(t.public_likes, "Vallie Ryan") AS array_contains_value FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "array_contains_value": true } ]
ARRAY_COUNT(expr
)
- Description
-
This function counts all the non-NULL values in the input
expr
array. - Arguments
-
- expr
-
[Required] The array to be searched and evaluate its values.
- Return Values
-
This function returns a count of all the non-
NULL
values in the array, or zero if there are no such values.If the
expr
argument isMISSING
, then it returnsMISSING
.If the
expr
argument isNULL
, then it returnsNULL
.If the
expr
argument is not an array, then it returnsNULL
.
Example 5: Use ARRAY_COUNT
to count the total hotel reviews.
SELECT ARRAY_COUNT(t.reviews) AS total_reviews FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "total_reviews": 2 } ]
ARRAY_DISTINCT(expr
)
- Description
-
This function returns a new array with distinct elements of the input array
expr
. - Arguments
-
- expr
-
[Required] The array of items to be evaluated.
- Return Values
-
An array with distinct elements of the input array
expr
.If the input
expr
isMISSING
, it returnsMISSING
.If the input
expr
is a non-array value, it returnsNULL
.
Example 6: Use ARRAY_DISTINCT
with a group of items.
SELECT ARRAY_DISTINCT(["apples","bananas","grapes","oranges","apples","mangoes","bananas"]) AS distinct_fruits; Results: [ { "distinct_fruits": [ "oranges", "grapes", "bananas", "mangoes", "apples" ] } ]
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 arrayexpr
.If one of the arguments is
MISSING
, it returnsMISSING
.If the input
expr
is a non-array, or if the inputdepth
argument is not an integer, it returnsNULL
.
Example 7a: 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_1level FROM default USE KEYS ["na"]; Results: [ { "flatten_by_1level": [ 1, 2, 31, 32, 33, 4, [ 511, 512 ], 52 ] } ]
Example 7b: Flatten the above example by 2 levels.
SELECT ARRAY_FLATTEN(b,2) AS flatten_by_2levels FROM default USE KEYS ["na"]; Results: [ { "flatten_by_2levels": [ 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. - Arguments
-
- expr
-
[Required] The array of values to be evaluated.
- Return Values
-
The first non-NULL value in the input array.
If the input
expr
is MISSING, then it returnsMISSING
.If the input
expr
is a non-array, then it returnsNULL
.
Example 9: Find the first non-NULL
value in an array of items.
SELECT ARRAY_IFNULL( ["","apples","","bananas","grapes","oranges"]) AS check_null; Results: [ { "check_null": "" } ]
Example 10: Find the first non-null
hotel reviewers.
SELECT ARRAY_IFNULL(t.public_likes) AS if_null FROM `travel-sample` t WHERE type="hotel" LIMIT 2; Results: [ { "if_null": "Julius Tromp I" }, { "if_null": null } ]
ARRAY_INSERT(expr
, pos
, val1
, val2
, …)
- Description
-
This function inserts the specified
value
or multiplevalue
items into the specifiedposition
in the input arrayexpression
, 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 returnsMISSING
.If the
expr
argument is a non-array or if theposition
argument is not an integer, then it returnsNULL
.
Example 11: Insert "jsmith" into the 2nd position of the public_likes
array.
SELECT ARRAY_INSERT(public_likes, 2, "jsmith") AS insert_val FROM `travel-sample` WHERE type = "hotel" LIMIT 1; Result:[ { "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
, ...)
(Introduced in Couchbase Server 4.5.1)
- 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 returnsMISSING
.If any of the input arguments are non-array values, then it returns
NULL
.
Example 12: Compare three arrays of fruit for common elements.
SELECT ARRAY_INTERSECT( ["apples","bananas","grapes","orange"], ["apples","orange"], ["apples","grapes"]) AS array_intersection; Result: [ { "array_intersection": [ "apples" ] } ]
Example 13: Compare three arrays of fruit with no common elements.
SELECT ARRAY_INTERSECT( ["apples","grapes","oranges"], ["apples"],["oranges"],["bananas", "grapes"]) AS array_intersection; Result: [ { "array_intersection": [] } ]
ARRAY_LENGTH(expr
)
- Description
-
This function returns the number of elements in the input array.
- Arguments
-
- expr
-
[Required] The array whose elements you want to know the number of.
- 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 14: Find how many total public_likes
there are in the travel-sample
bucket.
SELECT ARRAY_LENGTH(t.public_likes) AS total_likes FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Result: [ { "total_likes": 8 } ]
ARRAY_MAX(expr
)
- Description
-
This function returns the largest non-
NULL
, non-MISSING
array element, in N1QL collation order. - Arguments
-
- expr
-
[Required] The array whose elements you want to know the highest value of.
- Return Values
-
The largest non-
NULL
, non-MISSING
array element, in N1QL collation order.If the input
expr
isMISSING
, then it returnsMISSING
.If the input
expr
is a non-array value, then it returnsNULL
.
Example 15: Find the maximum (last) value of the public_likes
array.
SELECT ARRAY_MAX(t.public_likes) AS max_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "max_val": "Vallie Ryan" } ]
ARRAY_MIN(expr
)
- Description
-
This function returns the smallest non-
NULL
, non-MISSING
array element, in N1QL collation order. - Arguments
-
- expr
-
[Required] The array whose elements you want to know the lowest value of.
- Return Values
-
The smallest non-
NULL
, non-MISSING
array element, in N1QL collation order.If the input
expr
isMISSING
, then it returnsMISSING
.If the input
expr
is a non-array value, then it returnsNULL
.
Example 16: Find the minimum (first) value of the public_likes
array.
SELECT ARRAY_MIN(t.public_likes) AS min_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "min_val": "Brian Kilback" } ]
ARRAY_POSITION(expr
, val
)
- Description
-
This function returns the first position of the specified
value
within the arrayexpression
.The array position is zero-based, that is, the first position is 0.
- Arguments
-
- expr
-
[Required] The array you want to search through.
- val
-
[Required] The value you’re searching for and whose position you want to know.
- Return Values
-
An integer representing the first position of the input
val
, where the first position is 0.It returns -1 if the input
val
does not exist in the array.If one of the arguments is
MISSING
, it returnsMISSING
.If either of the arguments are non-array values, it returns
NULL
.
Example 17: Find which position "Brian Kilback" is in the public_likes
array.
SELECT ARRAY_POSITION(t.public_likes, "Brian Kilback") AS array_position FROM `travel-sample` t WHERE type="hotel" LIMIT 1; [ { "array_position": 4 } ]
ARRAY_PREPEND(val1
, val2
, … , expr
)
- Description
-
This function returns the new array after prepending the array
expr
with the specifiedval
or multipleval
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 arrayexpr
.If one of the arguments is
MISSING
, it returnsMISSING
.If the last argument is a non-array, it returns
NULL
.
Example 18: Prepend "Dave Smith" to the front of the public_likes
array.
SELECT ARRAY_PREPEND("Dave Smith",t.public_likes) AS prepend_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "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 multipleval
arguments appended if theval
is not already present. Otherwise, it returns the unmodified input arrayexpr
.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 multipleval
arguments appended if theval
is not already present. Otherwise, it returns the unmodified input arrayexpr
.If one of the arguments is
MISSING
, then it returnsMISSING
.If the first argument is a non-array, then it returns
NULL
.
Example 19: Append "Dave Smith" to the end of the public_likes
array.
SELECT ARRAY_PUT(t.public_likes, "Dave Smith") AS array_put FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "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 thanend_num
. Successive numbers are incremented bystep_int
.If
step_int
is not specified, then the default value is 1. Ifstep_num
is negative, then he function decrements until the smallest number greater thanend_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 thanend_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 returnsMISSING
.If any of the arguments do not start with a digit, then it returns an error.
Example 20a: Make an array from 0 to 20 by stepping every 5th number.
SELECT ARRAY_RANGE(0, 25, 5) AS gen_array_range_5; Results: [ { "gen_array_range_5": [ 0, 5, 10, 15, 20 ] } ]
Example 20b: 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; Results: [ { "gen_array_range_2": [ 0.1, 1.1 ] } ]
Example 20c: Make an array from 10 to 3 by stepping down every 3rd number.
SELECT ARRAY_RANGE(10, 3, -3) AS gen_array_range_minus3; Results: [ { "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 multiplevalue
fields removed from the arrayexpression
and 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 returnsMISSING
.If the first argument is not an array, then it returns
NULL
.
Example 21: Remove "Vallie Ryan" from the public_likes
array.
SELECT ARRAY_REMOVE(t.public_likes, "Vallie Ryan") AS remove_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "remove_val": [ "Julius Tromp I", "Corrine Hilll", "Jaeden McKenzie", "Brian Kilback", "Lilian McLaughlin", "Ms. Moses Feeney", "Elnora Trantow" ] } ]
ARRAY_REPEAT(val
, rep_int
)
- Description
-
This function returns a new array with the specified
val
repeatedrep_int
times. - 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 returnsMISSING
.If the
rep_int
argument is not an integer, then it returnsNULL
.
Example 22: Make an array with "Vallie Ryan" three times.
SELECT ARRAY_REPEAT("Vallie Ryan", 3) AS repeat_val; Results: [ { "repeat_val": [ "Vallie Ryan", "Vallie Ryan", "Vallie Ryan" ] } ]
ARRAY_REPLACE(expr
, val1
, val2
max_int
])
- Description
-
This function returns a new array with all occurrences of
value1
replaced withvalue2
.If
max_int
is specified, than no more thanmax_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 ofval1
replaced withval2
.If any of the arguments are
MISSING
, then it returnsMISSING
.If the first argument is not an array or if the second argument is
NULL
, then it returnsNULL
.
Example 23: Replace all occurrences of "Vallie Ryan" with "Valerie Ryan".
SELECT ARRAY_REPLACE(t.public_likes, "Vallie Ryan", "Valerie Ryan") AS replace_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "replace_val": [ "Julius Tromp I", "Corrine Hilll", "Jaeden McKenzie", "Valerie Ryan", "Brian Kilback", "Lilian McLaughlin", "Ms. Moses Feeney", "Elnora Trantow" ] } ]
ARRAY_REVERSE(expr
)
- Description
-
This function returns a new array with all the elements of
expr
in reverse order. - Arguments
-
- expr
-
[Required] The input array whose elements you want to reverse.
- Return Values
-
A new array with all the elements of
expr
in reverse order.If the argument is
MISSING
, then it returnsMISSING
.If the argument is a non-array value, then it returns
NULL
.
Example 24: Reverse the values in the public_likes
array.
SELECT ARRAY_REVERSE(t.public_likes) AS reverse_val FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "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 N1QL collation order. - Arguments
-
- expr
-
[Required] The input array you want sorted.
- Return Values
-
A new array with the elements of
expr
sorted in N1QL collation order.If the argument is
MISSING
, then it returnsMISSING
.If the argument is a non-array value, then it returns
NULL
.
Example 25: Sort the public_likes
array.
SELECT ARRAY_SORT(t.public_likes) AS sorted_array FROM `travel-sample` t WHERE type="hotel" LIMIT 1; Results: [ { "sorted_array": [ "Brian Kilback", "Corrine Hilll", "Elnora Trantow", "Jaeden McKenzie", "Julius Tromp I", "Lilian McLaughlin", "Ms. Moses Feeney", "Vallie Ryan" ] } ]
ARRAY_STAR(expr
)
- Description
-
This function converts an array of
expr
objects into an object of arrays. - Arguments
-
- expr
-
[Required] The input array you want to convert into an object of arrays.
- Output Values
-
An object of arrays.
If the argument is
MISSING
, then it returnsMISSING
.If the argument is a non-array value, then it returns
NULL
.
Example 26: 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; Results: [ { "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 ( doc.f[].id )*
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
)
- Description
-
This function returns the sum of all the non-
NULL
number values in theexpr
array. - Arguments
-
- expr
-
[Required] The input array of numbers you want to know the total value of.
- Return Values
-
The sum of all the non-
NULL
number values in theexpr
array.If there are no number values, then it returns 0 (zero).
If the argument is
MISSING
, then it returnsMISSING
.If the argument is a non-array value, then it returns
NULL
.
Example 27: Find the total of a given array of numbers.
SELECT ARRAY_SUM([0,1,1,2,3,5]) as sum; Results: [ { "sum": 12 } ]
ARRAY_SYMDIFF(expr1
, expr2
, …)
Synonym: ARRAY_SYMDIFF1(expression1, expression2, …)
- 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. - Arguments
-
- expr1, expr2, …
-
[At least 2 are Required] The input arrays to compare.
- 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 returnsMISSING
.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 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. |
Example 28: Find the elements that appear in exactly one of these three input arrays.
SELECT ARRAY_SYMDIFF([1, 2], [1, 2, 4], [1, 3]) AS symm_diff1; Results: [ { "symm_diff1": [ 3, 4 ] } ]
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.
- Arguments
-
- expr1, expr2, …
-
[At least 2 are Required] The input arrays to compare.
- 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 returnsMISSING
.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 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. |
Example 29: Find the elements that appear in an odd number of these three input arrays.
SELECT ARRAY_SYMDIFFN([1, 2], [1, 2, 4], [1, 3]) AS symm_diffn; Results: [ { "symm_diffn": [ 1, 3, 4 ] } ]
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.
- Arguments
-
- expr1, expr2, …
-
[At least 2 are Required] The input arrays to compare.
- Return Values
-
A new array with the set union of the input arrays.
If any of the arguments is
MISSING
, then it returnsMISSING
.If any of the arguments is a non-array value, then it returns
NULL
.
Example 30a: List the union of three given arrays.
SELECT ARRAY_UNION([1, 2], [1, 2, 4], [1, 3]) AS array_union; Results: [ { "array_union": [ 3, 2, 1, 4 ] } ]
Example 30b: List the union of two given arrays with a string.
SELECT ARRAY_UNION([1, 2], [1, 2, 4], "abc") AS array_union; Results: [ { "array_union": null } ]