FILTER

Takes an input array and returns an array containing only elements that meet a specified condition.

Takes an input array and returns an array containing only elements that meet a specified condition. This function uses null-safe equality checks when testing elements.

Behavior type

Immutable

Syntax

FILTER(array, lambda-expression )

Arguments

array
Input array.
lambda-expression

Lambda function to apply to each element. The function must return a Boolean value. The first argument to the function is the element, and the optional second element is the index of the element.

Examples

Given a table that contains names and arrays of email addresses, the following query filters out fake email addresses and returns the rest:

=> SELECT name, FILTER(email, e -> NOT REGEXP_LIKE(e,'example.com','i')) AS 'real_email'
   FROM people;
      name      |                   real_email
----------------+-------------------------------------------------
 Elaine Jackson | ["ejackson@somewhere.org","elaine@jackson.com"]
 Frank Adams    | []
 Lee Jones      | ["lee.jones@somewhere.org"]
 M Smith        | ["ms@msmith.com"]
(4 rows)

You can use the results in a WHERE clause to exclude rows that no longer contain any email addresses:

=> SELECT name, FILTER(email, e -> NOT REGEXP_LIKE(e,'example.com','i')) AS 'real_email'
   FROM people
   WHERE ARRAY_LENGTH(real_email) > 0;
      name      |                   real_email
----------------+-------------------------------------------------
 Elaine Jackson | ["ejackson@somewhere.org","elaine@jackson.com"]
 Lee Jones      | ["lee.jones@somewhere.org"]
 M Smith        | ["ms@msmith.com"]
(3 rows)

See also