Array Functions

[new in v8.0]

This section describes operators and functions that can be used with ARRAY field type of Valentina DB.

Comparison operators return 1 (TRUE), 0 (FALSE) or NULL values. These functions work a bit specific with arrays.

Common rules for such operators are still true:

• If one or both arguments is NULL then the result is also NULL.
• If the first argument is array then they are compared as arrays (Comparision type is based on first argument type, the second argument will be cast to the first one's type).

Specific rules are:

• Array comparisons compare the array items element-by-element, from left to right.
• NULL-items considered to be equal.
• NULL-item is greater than any not-NULL item.
• Any item is greater than absent item.

Operator Equal. Compare all array items, the result is true if all items are equal.

SELECT IF( ARRAY[1,2,3] = ARRAY[1,2,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,NULL,3] = ARRAY[1,NULL,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] = ARRAY[1,22,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2] = ARRAY[1,2,3], 1, 0 );
=> 0

Operator Not Equal. Compare array items until the first “equal” result.

2 forms are supported: <> - SQL Standard != - C syntax

SELECT IF( ARRAY[1,2,3] <> ARRAY[1,2,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,NULL,3] <> ARRAY[1,NULL,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] <> ARRAY[1,22,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2] <> ARRAY[1,2,3], 1, 0 );
=> 1

Operator Less Than Or Equal. Compare array items until the first “greater” result.

SELECT IF( ARRAY[1,2,3] <= ARRAY[1,2,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,NULL,3] <= ARRAY[1,NULL,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] <= ARRAY[1,NULL,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] <= ARRAY[1,22,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] <= ARRAY[1,22,0], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2] <= ARRAY[1,2,3], 1, 0 );
=> 1

Operator Less Than. Compare array items until the first “greater” result (if all items are equal - the result is false).

SELECT IF( ARRAY[1,2,3] < ARRAY[1,2,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,NULL,3] < ARRAY[1,NULL,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] < ARRAY[1,NULL,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] < ARRAY[1,22,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] < ARRAY[1,22,0], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2] < ARRAY[1,2,3], 1, 0 );
=> 1

Operator Greater Than Or Equal. Compare array items until the first “less” result.

SELECT IF( ARRAY[1,2,3] >= ARRAY[1,2,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,NULL,3] >= ARRAY[1,NULL,3], 1, 0 );
=> 1
SELECT IF( ARRAY[1,2,3] >= ARRAY[1,NULL,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] >= ARRAY[1,22,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] >= ARRAY[1,22,0], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2] >= ARRAY[1,2,3], 1, 0 );
=> 0

Operator Greater Than. Compare array items until the first “less” result (if all items are equal - the result is false).

SELECT IF( ARRAY[1,2,3] > ARRAY[1,2,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,NULL,3] > ARRAY[1,NULL,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] > ARRAY[1,NULL,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] > ARRAY[1,22,3], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2,3] > ARRAY[1,22,0], 1, 0 );
=> 0
SELECT IF( ARRAY[1,2] > ARRAY[1,2,3], 1, 0 );
=> 0

Returns the array item.

SELECT ARRAY['a','b','c'][1];
=> a

Appends one more item to the array and returns this array.

SELECT ARRAY_APPEND(ARRAY[1,2,3], 4);
=> [1,2,3,4]

Concatenate two arrays and returns the resulting array.

SELECT ARRAY_CONCAT(ARRAY[1,2,3], ARRAY[4,5]);
=> [1,2,3,4,5]

Returns 1 if inArray1 contains all items of inArray2.

SELECT ARRAY_CONTAINS(ARRAY[1,2,3], ARRAY[1,2,3]);
=> 1
SELECT ARRAY_CONTAINS(ARRAY[1,2,3], ARRAY[3,2,1]);
=> 1
SELECT ARRAY_CONTAINS(ARRAY[1,2,3], ARRAY[1,2]);
=> 1
SELECT ARRAY_CONTAINS(ARRAY[1,NULL,3], ARRAY[NULL]);
=> 1
SELECT ARRAY_CONTAINS(ARRAY[1,2,3], ARRAY[1,4]);
=> 0

Fills inArray with the supplied value.

SELECT ARRAY_FILL(ARRAY[1,2,3], 7);
=> [7,7,7]
SELECT ARRAY_FILL(ARRAY['1','2','3'], 7);
=> ['7','7','7']
SELECT ARRAY_FILL(ARRAY[1,2,3], NULL);
=> [NULL,NULL,NULL]
SELECT ARRAY_FILL(NULL, 7);
=> NULL
SELECT ARRAY_FILL('a', 7);
=> NULL

Builds the array from a JSON string.

SELECT JSON_TO_ARRAY( '[1,2,3,5,null]' );
=> [1,2,3,5,NULL]
 
SELECT JSON_TO_ARRAY( '["Ivanov","Petrov",null]' );
=> ['Ivanov','Petrov',NULL]

Returns the number of elements in the array.

SELECT ARRAY_LENGTH(ARRAY[1,2,3]);
=> 3

Returns 1 if inArray1 contains any items of inArray2.

SELECT ARRAY_OVERLAP(ARRAY[1,2,3], ARRAY[1,22,3]);
=> 1
SELECT ARRAY_OVERLAP(ARRAY[1,2,3], ARRAY[33,22,1]);
=> 1
SELECT ARRAY_OVERLAP(ARRAY[1,2,3], ARRAY[1,22]);
=> 1
SELECT ARRAY_OVERLAP(ARRAY[1,2], ARRAY[1,22,33]);
=> 1
SELECT ARRAY_OVERLAP(ARRAY[1,NULL,3], ARRAY[NULL,33]);
=> 1
SELECT ARRAY_OVERLAP(ARRAY[1,2,3], ARRAY[11,4]);
=> 0

Returns the subscript of the first occurrence of the second argument in the array, starting at the element indicated by the third argument or at the first element.

SELECT ARRAY_POSITION(ARRAY['red','black','white','blue','yellow'], 'white');
=> 3

SELECT ARRAY_POSITION(ARRAY['red','black','white','blue','white'], 'white', 4);
=> 5

Returns an array of subscripts of all occurrences of the second argument in the array given as first argument.

SELECT ARRAY_POSITIONS(ARRAY['A','A','B','A'], 'A');
=> [1,2,4]

Append an element to the beginning of an array.

SELECT ARRAY_PREPEND(4, ARRAY[1,2,3]);
=> [4,1,2,3]

Remove all elements equal to the given value from the array.

SELECT ARRAY_REMOVE(ARRAY[1,2,3,2], 2);
=> [1,3]

Replace each array element equal to the given value with a new value.

SELECT ARRAY_REPLACE(ARRAY[1,2,5,4], 5, 3);
=> [1,2,3,4]

Set array inIndex's element to the given new value.

SELECT ARRAY_SET(ARRAY[1,2,5,4], 3, 33);
=> [1,2,33,4]

Returns the array as a JSON array.

SELECT ARRAY_TO_JSON(ARRAY[1,2,3,5,NULL]);
=> '[1,2,3,5,null]'
 
SELECT ARRAY_TO_JSON(ARRAY['Ivanov','Petrov',NULL]);
=> '["Ivanov","Petrov",null]'

Concatenates array elements using supplied delimiter and optional null string.

SELECT ARRAY_TO_STRING(ARRAY[1,2,5,4], ',');
=> '1,2,5,4'
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,5,NULL], ',');
=> '1,2,3,5'
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,NULL,5], ',');
=> '1,2,3,5'
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,5,NULL], ',', '*');
=> '1,2,3,5,*'
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,NULL,5], ',', '*');
=> '1,2,3,*,5'
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,NULL,5], NULL);
=> ''
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,NULL,5], NULL, '*');
=> ''
 
SELECT ARRAY_TO_STRING(ARRAY[1,2,3,NULL,5], ',', NULL); 
=> '1,2,3,5'