Categories:

Semi-structured and structured data functions (Higher-order)

FILTER

Filters an array based on the logic in a Lambda expression.

See also:

Filter and transform data with Snowflake higher-order functions

Syntax

FILTER( <array> , <lambda_expression> )
Copy

Arguments

array

The array that contains the elements to be filtered. The array can be semi-structured or structured.

lambda_expression

A Lambda expression that defines the filter condition on each array element.

The Lambda expression must have only one argument specified in the following syntax:

<arg> [ <datatype> ] -> <expr>
Copy

Returns

The return type of this function is an array of the same type as the input array. The returned array contains the elements for which he filter condition returns TRUE.

If either argument is NULL, the function returns NULL without reporting an error.

Usage notes

  • When the data type for the Lambda argument is explicitly specified, the array element is coerced into the specified type before Lambda invocation. For information about coercion, see Data Type Conversion.

  • If the filter condition evaluates to NULL, the corresponding array element is filtered out.

Examples

The following examples use the FILTER function.

Filter for array elements greater than a value

Use the FILTER function to return objects in an array that have a value greater than or equal to 50:

SELECT FILTER(
  [
    {'name':'Pat', 'value': 50},
    {'name':'Terry', 'value': 75},
    {'name':'Dana', 'value': 25}
  ],
  a -> a:value >= 50) AS "Filter >= 50";
Copy
+----------------------+
| Filter >= 50         |
|----------------------|
| [                    |
|   {                  |
|     "name": "Pat",   |
|     "value": 50      |
|   },                 |
|   {                  |
|     "name": "Terry", |
|     "value": 75      |
|   }                  |
| ]                    |
+----------------------+

Filter for array elements that are not NULL

Use the FILTER function to return array elements that are not NULL:

SELECT FILTER([1, NULL, 3, 5, NULL], a -> a IS NOT NULL) AS "Not NULL Elements";
Copy
+-------------------+
| Not NULL Elements |
|-------------------|
| [                 |
|   1,              |
|   3,              |
|   5               |
| ]                 |
+-------------------+

Filter for array elements in a table that are greater than or equal to a value

Assume you have a table named orders with the columns order_id, order_date, and order_detail. The order_detail column is an array of the line items, their purchase quantity, and subtotal. The table contains two rows of data. The following SQL statement creates this table and inserts the rows:

CREATE OR REPLACE TABLE orders AS
  SELECT 1 AS order_id, '2024-01-01' AS order_date, [
    {'item':'UHD Monitor', 'quantity':3, 'subtotal':1500},
    {'item':'Business Printer', 'quantity':1, 'subtotal':1200}
  ] AS order_detail
  UNION SELECT 2 AS order_id, '2024-01-02' AS order_date, [
    {'item':'Laptop', 'quantity':5, 'subtotal':7500},
    {'item':'Noise-cancelling Headphones', 'quantity':5, 'subtotal':1000}
  ];

SELECT * FROM orders;
Copy
+----------+------------+--------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL                               |
|----------+------------+--------------------------------------------|
|        1 | 2024-01-01 | [                                          |
|          |            |   {                                        |
|          |            |     "item": "UHD Monitor",                 |
|          |            |     "quantity": 3,                         |
|          |            |     "subtotal": 1500                       |
|          |            |   },                                       |
|          |            |   {                                        |
|          |            |     "item": "Business Printer",            |
|          |            |     "quantity": 1,                         |
|          |            |     "subtotal": 1200                       |
|          |            |   }                                        |
|          |            | ]                                          |
|        2 | 2024-01-02 | [                                          |
|          |            |   {                                        |
|          |            |     "item": "Laptop",                      |
|          |            |     "quantity": 5,                         |
|          |            |     "subtotal": 7500                       |
|          |            |   },                                       |
|          |            |   {                                        |
|          |            |     "item": "Noise-cancelling Headphones", |
|          |            |     "quantity": 5,                         |
|          |            |     "subtotal": 1000                       |
|          |            |   }                                        |
|          |            | ]                                          |
+----------+------------+--------------------------------------------+

Use the FILTER function to return orders with subtotals that are greater than or equal to 1500:

SELECT order_id,
       order_date,
       FILTER(o.order_detail, i -> i:subtotal >= 1500) ORDER_DETAIL_GT_EQUAL_1500
  FROM orders o;
Copy
+----------+------------+----------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL_GT_EQUAL_1500 |
|----------+------------+----------------------------|
|        1 | 2024-01-01 | [                          |
|          |            |   {                        |
|          |            |     "item": "UHD Monitor", |
|          |            |     "quantity": 3,         |
|          |            |     "subtotal": 1500       |
|          |            |   }                        |
|          |            | ]                          |
|        2 | 2024-01-02 | [                          |
|          |            |   {                        |
|          |            |     "item": "Laptop",      |
|          |            |     "quantity": 5,         |
|          |            |     "subtotal": 7500       |
|          |            |   }                        |
|          |            | ]                          |
+----------+------------+----------------------------+