- Categories:
Semi-structured and structured data functions (Higher-order)
FILTER¶
Filters an array based on the logic in a lambda expression.
Syntax¶
FILTER( <array> , <lambda_expression> )
Arguments¶
arrayThe array that contains the elements to be filtered. The array can be semi-structured or structured.
lambda_expressionA 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>
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 the 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";
+----------------------+
| 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";
+-------------------+
| 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-canceling Headphones', 'quantity':5, 'subtotal':1000}
] AS order_detail;
SELECT * FROM orders;
+----------+------------+-------------------------------------------+
| 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-canceling 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;
+----------+------------+----------------------------+
| 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 |
| | | } |
| | | ] |
+----------+------------+----------------------------+