カテゴリ：: 半構造化データ関数と構造化データ関数（高次）

FILTER¶

ラムダ式のロジックに基づいて、配列をフィルターします。

こちらもご参照ください。: Snowflakeの高階関数でラムダ関数をデータ上で使用する

構文¶

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           |
| ]              |
+----------------+