LAMBDA expressions and functions - Hologres - Alibaba Cloud Documentation Center

Hologres V3.2 and later support LAMBDA expressions and five higher-order array functions: HG_ARRAY_MAP, HG_ARRAY_FILL, HG_ARRAY_FILTER, HG_ARRAY_SORT, and HG_ARRAY_FIRST_INDEX. All five functions accept a LAMBDA expression as their first argument and apply it element-wise to one or more input arrays.

LAMBDA expressions

A LAMBDA expression defines an anonymous function inline, without a name or explicit type declarations.

Syntax

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

Parameters

Component	Description
`LAMBDA[]`	Keyword that declares a LAMBDA expression.
`(lambda_arg1, ..., lambda_argn)`	Input parameters. You can define any number of parameters without specifying data types. Enclose multiple parameters in parentheses `()`.
`=>`	Operator that separates the parameter list from the expression body.
`expr`	Expression body. Only scalar expressions are supported. The body can reference input parameters and table column names. Nested LAMBDA expressions are also supported—a higher-order array function that contains a LAMBDA can appear inside a scalar expression.

For more information about PostgreSQL expressions, see Expressions.

Limitations

The expression body does not support:

Aggregate functions (e.g., LAMBDA[x => max(x)])
Window functions
Subqueries (e.g., LAMBDA[x => x + (SELECT 1)])

Example

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

Higher-order array functions

All five functions share the same signature pattern:

FUNCTION_NAME(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Common requirements

At least one argument must be non-constant.
When multiple arrays are specified, they must all have the same length.
All functions return NULL if any input array is NULL.

HG_ARRAY_MAP

Applies a LAMBDA expression to the corresponding elements of one or more arrays and returns a new array of results.

Syntax

HG_ARRAY_MAP(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Parameters

Parameter	Description
`LAMBDA[func(x1 [, ..., xN])]`	LAMBDA expression to apply to each set of corresponding elements.
`source_arr`	Input array. When multiple arrays are specified, they must have the same length.

Return value

Returns an array whose element type matches the return type of the LAMBDA expression. The length equals the length of the first input array. Returns NULL if any input array is NULL.

Examples

The following examples use this sample table:

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);

Add a column value to each array element (column capture)

A LAMBDA body can reference columns from the same row, not just array elements. This example adds col3 to each element of arr_col1:

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

Result:

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

Add elements from two arrays and include a subarray aggregate

This example adds corresponding elements from arr_col1 and the constant array ARRAY[5,6,7], then adds the minimum value of arr_col2:

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;

Result:

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

Use a nested LAMBDA expression

This example nests HG_ARRAY_MAP inside another HG_ARRAY_MAP. It first adds col3 to each element of arr_col2, takes the minimum of that intermediate array, and then adds the result to each 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;

Result:

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

HG_ARRAY_FILL

Scans the input arrays from left to right. At each position, the LAMBDA expression is evaluated: if it returns TRUE, the corresponding element from the first input array becomes the current fill value, which is then propagated forward to each subsequent position until the next TRUE result. If the LAMBDA returns FALSE at the first position, the first element is kept as-is.

Syntax

HG_ARRAY_FILL(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Parameters

Parameter	Description
`LAMBDA[func(x1 [, ..., xN])]`	LAMBDA expression that returns a Boolean value for each position.
`source_arr`	Input array. When multiple arrays are specified, they must have the same length.

Return value

Returns an array with the same data type and length as the first input array. Returns NULL if any input array is NULL.

Examples

The following examples use this sample table:

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);

Forward-fill when arr_col2 element is greater than 0

Positions where arr_col2 > 0 evaluate to TRUE, so the corresponding arr_col1 element becomes the fill value and is propagated to each subsequent position until the next TRUE:

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

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)

Forward-fill when arr_col2 element is less than or equal to 0

The condition is reversed: positions where arr_col2 <= 0 evaluate to TRUE. Because the first element of arr_col2 is 1 (not <= 0), the LAMBDA returns FALSE at position 1 and the first element is kept as-is:

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

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

Applies a LAMBDA expression to the corresponding elements of the input arrays to produce a Boolean array, then keeps only the elements of the first array where the Boolean value is TRUE.

Syntax

HG_ARRAY_FILTER(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Parameters

Parameter	Description
`LAMBDA[func(x1 [, ..., xN])]`	LAMBDA expression that returns a Boolean value for each element.
`source_arr`	Input array. When multiple arrays are specified, they must have the same length.

Return value

Returns an array with the same data type as the first input array, containing only the elements where the LAMBDA returned TRUE. Returns NULL if any input array is NULL.

Examples

The following examples use this sample table:

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);

Keep elements where the corresponding arr_col2 element is greater than 0

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

Result:

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

id=2 returns NULL because arr_col1 is NULL. For id=3, all elements of arr_col2 are greater than 0, so all elements of arr_col1 are kept.

Filter using a composite condition with column references

This example keeps elements where any of the following is true: the element in arr_col1 is 0, col3 is NULL, or the sum of arr_col1[i] + arr_col2[i] + col3 + col4 exceeds 20. The query uses tbl1 from the HG_ARRAY_MAP examples:

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;

Result:

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

HG_ARRAY_SORT

Applies a LAMBDA expression to the input arrays to derive a sort-key array, then returns the first input array sorted in ascending order of those keys.

Syntax

HG_ARRAY_SORT(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Parameters

Parameter	Description
`LAMBDA[func(x1 [, ..., xN])]`	LAMBDA expression that produces the sort key for each element.
`source_arr`	Input array. When multiple arrays are specified, they must have the same length.

Return value

Returns an array with the same data type and length as the first input array, sorted by the computed sort keys in ascending order. Returns NULL if any input array is NULL.

Examples

Sort a constant array by the values in another column

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 constant array ARRAY[4,5,6] is reordered according to the ascending order of arr_col1 values. Result:

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

Sort a text array by an integer sort-key array, with NULL handling

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;

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)

For id=2, arr_col2 is NULL so the function returns NULL.

HG_ARRAY_FIRST_INDEX

Applies a LAMBDA expression to the input arrays to produce a Boolean array and returns the 1-based index of the first TRUE element. Returns 0 if no element is TRUE.

Syntax

HG_ARRAY_FIRST_INDEX(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);

Parameters

Parameter	Description
`LAMBDA[func(x1 [, ..., xN])]`	LAMBDA expression that returns a Boolean value for each element.
`source_arr`	Input array. When multiple arrays are specified, they must have the same length.

Return value

Returns the 1-based index of the first element for which the LAMBDA returns TRUE. Returns 0 if no element is TRUE. Returns NULL if any input array is NULL.

Example

Find the index of the first element in arr_col1 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;

Result:

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

For id=1, the first element >= 3 is at index 3 (value 3). For id=2, arr_col1 is NULL so the function returns NULL.