LAMBDA expressions and related functions - Hologres - Alibaba Cloud Documentation Center

Hologres V3.2 and later versions support LAMBDA expressions and higher-order array functions. This topic describes their syntax and usage.

LAMBDA expressions

Syntax

LAMBDA [ (lambda_arg1, ..., lambda_argn) => expr ]

Parameters
- LAMBDA[]: The keyword used to declare a LAMBDA expression.
- (lambda_arg1, ..., lambda_argn) => expr: Defines the LAMBDA function. It consists of the following parts:
  - (lambda_arg1, ..., lambda_argn): The input parameters for the LAMBDA function. You can define any number of input parameters without specifying their data types. Multiple input parameters must be enclosed in parentheses ().
  - expr: The expression body. Only scalar expressions are supported. You can use input parameters and reference table column names. Nested LAMBDA expressions are also supported. This means you can use higher-order ARRAY functions that contain LAMBDA expressions within a scalar expression. However, aggregate functions, window functions, or subqueries are not supported.
    For more information about expressions in PostgreSQL, see Expressions.
  - =>: The operator that separates the input parameters from the expression.

Example

LAMBDA [ (x1, x2, x3) => x2 - x1 * x3 + array_min(arr_col) ]

Higher-order array functions

HG_ARRAY_MAP

Description: Applies a LAMBDA expression to the corresponding elements of one or more input arrays and returns a new array containing the results.
```
HG_ARRAY_MAP(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
Usage notes
This function requires at least one non-constant argument.
Parameters
- LAMBDA[func(x1 [, ..., xN])]: Defines the LAMBDA expression.
- source_arr: The input array. If you specify multiple arrays, they must have the same length.
Return value
Returns an array. The data type of the returned array is the same as the return value type of the expression. The length of the returned array is the same as the length of the first input array. The function returns NULL if any input array is NULL.

Examples

Sample data

DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl1 VALUES(1, ARRAY[1,2,3], ARRAY[11,12,13],1,2);
INSERT INTO tbl1 VALUES(2, ARRAY[21,22,23], ARRAY[31,32,33],10,20);

Example 1: Add the value of col3 to each element of arr_col1.

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[x => x + col3], arr_col1)
FROM
    tbl1
ORDER BY
    id;

The query returns the following result:

id | hg_array_map 
----+--------------
  1 | {2,3,4}
  2 | {31,32,33}
(2 rows)

Example 2: Add the corresponding elements of arr_col1 and a constant array, and then add the minimum value of the arr_col2 array.

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[(x, y) => y + x + array_min (arr_col2)], arr_col1, ARRAY[5,6,7])
FROM
    tbl1
ORDER BY
    id;

The query returns the following result:

id | hg_array_map 
----+--------------
  1 | {17,19,21}
  2 | {57,59,61}
(2 rows)

Example 3: Use a nested LAMBDA expression to add col3 to each element of arr_col2, and then add the minimum value of the resulting array to each corresponding element of arr_col1.

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[x => x + array_min (HG_ARRAY_MAP (LAMBDA[a => a + col3], arr_col2))], arr_col1)
FROM
    tbl1
ORDER BY
    id;

The query returns the following result:

id | hg_array_map 
----+--------------
  1 | {13,14,15}
  2 | {62,63,64}
(2 rows)

HG_ARRAY_FILL

Description: Applies a LAMBDA expression to the input arrays to generate a BOOLEAN array, which is used to fill the first input array. The process starts at the lowest index: if an element in the BOOLEAN array is TRUE, the corresponding element from the first input array becomes the current fill value. This fill value is propagated to subsequent elements until the next TRUE element is encountered. The function returns the final filled array.
```
HG_ARRAY_FILL(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
Usage notes
This function requires at least one non-constant argument.
Parameters
- LAMBDA[func(x1 [, ..., xN])]: Defines the LAMBDA expression.
- source_arr: The input array. If you specify multiple arrays, they must have the same length.
Return value
Returns an array. The data type and length of the returned array are the same as those of the first input array. If an input array is NULL, this function returns NULL.

Examples

Sample data

DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl2 VALUES(1, ARRAY[1,2,3,4,5,6,7,8,9],ARRAY[1,0,0,1,0,0,0,1,0],1,2);
INSERT INTO tbl2 VALUES(2, ARRAY[10,12,13,14,15,16,17,18,19],ARRAY[1,0,0,1,0,0,0,1,0],1,2);

Example 1: If an element in arr_col2 is greater than 0, the corresponding element from arr_col1 is used as the fill value, and subsequent elements are replaced with this fill value.

SELECT
    id,
    HG_ARRAY_FILL (LAMBDA[(x, y) => y > 0], arr_col1, arr_col2)
FROM
    tbl2
ORDER BY
    id;

The query returns the following result:

id |        hg_array_fill         
----+------------------------------
  1 | {1,1,1,4,4,4,4,8,8}
  2 | {10,10,10,14,14,14,14,18,18}
(2 rows)

Example 2: If an element in arr_col2 is less than or equal to 0, the corresponding element from arr_col1 is used as the fill value. The first element of arr_col2 is greater than 0, and the first element of the result is the original value from arr_col1.
```
SELECT
    id,
    HG_ARRAY_FILL (LAMBDA[(x, y) => y <= 0], arr_col1, arr_col2)
FROM
    tbl2
ORDER BY
    id;
```
The query returns the following result:
```
id |        hg_array_fill         
----+------------------------------
  1 | {1,2,3,3,5,6,7,7,9}
  2 | {10,12,13,13,15,16,17,17,19}
(2 rows)
```

HG_ARRAY_FILTER

Description: Applies a LAMBDA expression to the corresponding elements of the input arrays to generate a BOOLEAN array. The function then filters the first input array, keeping only the elements where the corresponding value in the BOOLEAN array is TRUE. The function returns an array of the filtered elements.
```
HG_ARRAY_FILTER(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
Usage notes
This function requires at least one non-constant argument.
Parameters
- LAMBDA[func(x1 [, ..., xN])]: Defines the LAMBDA expression.
- source_arr: The input array. If you specify multiple arrays, they must have the same length.
Return value
Returns an array. The data type of the returned array is the same as that of the first input array. If an input array is NULL, this function returns NULL.

Examples

Sample data

DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl3 VALUES(1, ARRAY[0,2,3,4,5,6,7,0,9], ARRAY[1,0,0,1,0,0,0,1,18],1,2);
INSERT INTO tbl3 VALUES(2, NULL, ARRAY[31,32,33,34,35,36,37,38,39],10,20);
INSERT INTO tbl3 VALUES(3, ARRAY[0,2,3,4,5,6,7,0,9], ARRAY[11,12,13,14,15,16,17,18,19],NULL,2);

Example 1: Filters arr_col1, keeping only the elements where the corresponding element in arr_col2 is greater than 0.

SELECT
    id,
    HG_ARRAY_FILTER (LAMBDA[(x, y) => y > 0], arr_col1, arr_col2)
FROM
    tbl3
ORDER BY
    id;

The query returns the following result:

id |   hg_array_filter   
----+---------------------
  1 | {0,4,0,9}
  2 | 
  3 | {0,2,3,4,5,6,7,0,9}
(3 rows)

Example 2: Filters arr_col1 based on a BOOLEAN array generated by the following LAMBDA conditions: an element in arr_col1 is 0, col3 is NULL, or the sum of the corresponding elements from arr_col1 and arr_col2 plus col3 and col4 is greater than 20.

SELECT
    id,
    HG_ARRAY_FILTER (
        LAMBDA[(x, y) => 
            (x = 0) 
            OR (col3 IS NULL) 
            OR (x + y + col3 + col4 > 20)]
        , arr_col1, arr_col2)
FROM
    tbl1
ORDER BY
    id;

The query returns the following result:

id |   hg_array_filter   
----+---------------------
  1 | {}
  2 | {21,22,23}
(2 rows)

HG_ARRAY_SORT

Description: Applies a LAMBDA expression to the input arrays to generate a sort-key array. The function then sorts the first input array based on the ascending order of this sort-key array and returns the result.
```
HG_ARRAY_SORT(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
Usage notes
This function requires at least one non-constant argument.
Parameters
- LAMBDA[func(x1 [, ..., xN])]: Defines the LAMBDA expression.
- source_arr: The input array. If you specify multiple arrays, they must have the same length.
Return value
Returns an array. The data type and length of the returned array are the same as those of the first input array. If an input array is NULL, this function returns NULL.

Examples

Sort the constant array ARRAY[4,5,6] based on the order of the elements in the arr_col1 array.

DROP TABLE IF EXISTS tbl4;
CREATE TABLE tbl4(id INT, arr_col1 INT[]);
INSERT INTO tbl4 VALUES(1, ARRAY[3,1,2]);
INSERT INTO tbl4 VALUES(2, ARRAY[2,3,1]);
INSERT INTO tbl4 VALUES(3, ARRAY[1,2,3]);
INSERT INTO tbl4 VALUES(4, NULL);

SELECT
    id,
    HG_ARRAY_SORT (LAMBDA[(x,y) => y], ARRAY[4,5,6], arr_col1)
FROM
    tbl4
ORDER BY
    id;

The query returns the following result:

id | hg_array_sort 
----+---------------
  1 | {5,6,4}
  2 | {6,4,5}
  3 | {4,5,6}
  4 | 
(4 rows)

Sort the arr_col1 array based on the order of the elements in the arr_col2 array.

DROP TABLE IF EXISTS tbl5;
CREATE TABLE tbl5(id INT, arr_col1 TEXT[], arr_col2 INT[]);
INSERT INTO tbl5 VALUES(1, ARRAY['1','2','3','4','5'], ARRAY[1,2,3,4,5]);
INSERT INTO tbl5 VALUES(2, ARRAY['1','2','3','4','5'], NULL);
INSERT INTO tbl5 VALUES(3, ARRAY['1','2','3','4','5'], ARRAY[21, 22, 20, 24, 25]);
INSERT INTO tbl5 VALUES(4, ARRAY['1','2','3','4','5'], ARRAY[21, 24, 22, 25, 23]);
INSERT INTO tbl5 VALUES(5, ARRAY['1','2','3','4','5'], ARRAY[21, 22, NULL, 24, 25]);

SELECT
    id,
    HG_ARRAY_SORT (LAMBDA[(x, y) => y], arr_col1, arr_col2)
FROM
    tbl5
ORDER BY
    id;

The query returns the following result:

id | hg_array_sort 
----+---------------
  1 | {1,2,3,4,5}
  2 | 
  3 | {3,1,2,4,5}
  4 | {1,3,5,2,4}
  5 | {3,1,2,4,5}
(5 rows)

HG_ARRAY_FIRST_INDEX

Description: Applies a LAMBDA expression to the input arrays to compute a BOOLEAN array. The function returns the index of the first element that is TRUE. If no element is TRUE, the function returns 0.
```
HG_ARRAY_FIRST_INDEX(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
Usage notes
This function requires at least one non-constant argument.
Parameters
- LAMBDA[func(x1 [, ..., xN])]: Defines the LAMBDA expression.
- source_arr: The input array. If you specify multiple arrays, they must have the same length.

Example

Calculates the index of the first element in the arr_col1 array that is greater than or equal to 3.

DROP TABLE IF EXISTS tbl6;
CREATE TABLE tbl6(id INT, arr_col1 INT[]);
INSERT INTO tbl6 VALUES(1, ARRAY[1,2,3,4,5]);
INSERT INTO tbl6 VALUES(2, NULL);

SELECT
    id,
    HG_ARRAY_FIRST_INDEX (LAMBDA[x => x >= 3], arr_col1)
FROM
    tbl6
ORDER BY
    id;

The query returns the following result:

id | hg_array_first_index 
----+----------------------
  1 |          3
  2 | 
(2 rows)