- カテゴリ：
半構造化データ関数と構造化データ関数 （高次）
FILTER¶
ラムダ式のロジックに基づいて、 配列 をフィルターします。
- こちらもご参照ください。
構文¶
FILTER( <array> , <lambda_expression> )
引数¶
arrayフィルターする要素を含む配列。配列は半構造化でも構造化でもかまいません。
lambda_expression各配列要素に対するフィルター条件を定義する ラムダ式。
ラムダ式には、以下の構文で指定された引数1つのみを使用する必要があります。
<arg> [ <datatype> ] -> <expr>
戻り値¶
この関数の戻り値は、入力配列と同じ型の配列です。返される配列には、フィルター条件が TRUE を返す要素が含まれます。
引数が NULL の場合、関数はエラーを報告せずに NULL を返します。
使用上の注意¶
ラムダ引数のデータ型が明示的に指定されている場合、配列要素はラムダ呼び出しの前に指定された型に強制されます。強制については、 データ型の変換 をご参照ください。
フィルター条件が NULL と評価された場合、対応する配列要素はフィルターから除外されます。
例¶
以下の例では、 FILTER 関数を使用しています。
値より大きい配列要素をフィルターする¶
FILTER 関数を使用して、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 |
| } |
| ] |
+----------------------+
NULL ではない配列要素をフィルターする¶
NULL ではない配列要素を返すには FILTER 関数を使用します。
SELECT FILTER([1, NULL, 3, 5, NULL], a -> a IS NOT NULL) AS "Not NULL Elements";
+-------------------+
| Not NULL Elements |
|-------------------|
| [ |
| 1, |
| 3, |
| 5 |
| ] |
+-------------------+
テーブル内の配列要素のうち、指定した値以上のものをフィルターする¶
order_id、 order_date、 order_detail の列を持つ orders という名前のテーブルがあるとします。order_detail 列は、行項目、購入数量、小計の配列です。テーブルには2行のデータがあります。次の SQL ステートメントは、このテーブルを作成し、行を挿入します。
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 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+
FILTER 関数を使用して、1500以上の小計を持つ注文を返します。
SELECT order_id,
order_date,
FILTER(o.order_detail, i -> i:subtotal >= 1500) AS 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 |
| | | } |
| | | ] |
+----------+------------+----------------------------+
ラムダ式でテーブル列を参照し、テーブルデータの配列要素をフィルタリングします¶
ARRAY 型の列とINT 型の列を持つテーブルを作成します。
CREATE OR REPLACE TABLE filter_column_ref_demo AS
SELECT [ 10, 15, 20 ] AS col1, 18 AS col2
UNION
SELECT [ 30, 50, 70 ] AS col1, 40 AS col2;
SELECT * FROM filter_column_ref_demo;
+-------+------+
| COL1 | COL2 |
|-------+------|
| [ | 18 |
| 10, | |
| 15, | |
| 20 | |
| ] | |
| [ | 40 |
| 30, | |
| 50, | |
| 70 | |
| ] | |
+-------+------+
FILTER 関数を使用して、各行の配列要素値のうち col2 より小さい値を返します。
SELECT FILTER(col1, v -> v < col2) AS filter_col_ref
FROM filter_column_ref_demo;
+----------------+
| FILTER_COL_REF |
|----------------|
| [ |
| 10, |
| 15 |
| ] |
| [ |
| 30 |
| ] |
+----------------+