filter

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Get a subset of rows from table that match one or more condition.

Syntax

filter(table, condition, condition, ...)

Examples

filter(orders, orders.status == 'refunded') //-> Table(orders.id, orders.status, ...)
filter(orders, orders.is_cancelled) // -> Table(orders.id, orders.status, ...)

// with pipe
orders | filter(orders.status == 'refunded') //-> Table(orders.id, orders.status, ...)
orders
| select(orders.id, orders.status)
| filter(orders.status == 'refunded') //-> Table(orders.id, orders.status)

// multiple conditions
orders
| filter(orders.status == 'refunded', orders.is_cancelled)
| select(orders.id) //-> Table(orders.id)

Input

• table: A model reference or the returned table from a previous expression.

• condition (repeatable): A formula returning a truefalse value. Each condition is evaluated over each row of table:

  • If the condition evaluates to true, the row is included in the output table.
  • If the condition evaluates to false, the row is excluded from the output table.
  info

  If multiple condition are provided, they are evaluated as a logical AND. For example, filter(orders, orders.status == refunded', orders.is_cancelled) is equivalent to filter(orders, and(orders.status == refunded', orders.is_cancelled)).

Output

A new table that contains all rows in table that match the condition.

Sample Usages

List all orders from Viet Nam created in August 2022

orders 
| filter(countries.name == "Vietnam", orders.created_at matches @2022-08)
| select(orders.id, orders.user_id)

Open in AMQL playground

Result

SQL

SELECT
  "orders"."id" AS "orders->id",
  "orders"."user_id" AS "orders->user_id"
FROM
  "demo"."orders" "orders"
  LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  LEFT JOIN "demo"."cities" "cities" ON "users"."city_id" = "cities"."id"
  LEFT JOIN "demo"."countries" "countries" ON "cities"."country_code" = "countries"."code"
WHERE
  ("countries"."name" = 'Vietnam') AND
  (
    (CAST ( "orders"."created_at" AS timestamptz ) >= CAST ( '2022-08-01T00:00:00.000+07:00' AS timestamptz )) AND
    (CAST ( "orders"."created_at" AS timestamptz ) < CAST ( '2022-09-01T00:00:00.000+07:00' AS timestamptz ))
  )

Any expression that can be used in select column, can be used in filter condition.

List Order Status with more than 1000 orders

orders | group(orders.status) | filter(count(orders.id) > 1000)
// is equivalent to 
// orders 
// | group(orders.status) 
// | select(orders.status, condition: count(orders.id) > 1000)
// | filter(condition)

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "orders"."status" AS "orders->status",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
)
SELECT
  "aql__t1"."orders->status" AS "orders->status"
FROM
  "aql__t1"
WHERE
  "aql__t1"."count_orders->id" > 1000