select

info

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

Definition

Run one expression or multiple expression over each row of a table and use the output to return a new table with the same number of row. In AQL, the select function serves as an intermediate step for subsequent transformations.

Syntax

select(table, expr1, expr2, ...)
select(table, col_name: expr1, ...)

Examples

select(orders, orders.id, orders.status) // -> Table(orders.id, orders.status)

// with pipe
orders | select(orders.id, orders.status) | select(orders.id) // -> Table(orders.id)

// named column
orders | select(orders.status, formatted_status: concat('Status: ', orders.status))

Input

• table: A model reference or the returned table from a previous expression.
• expr (repeatable): An expression to evaluate for each row of table.
  caution

  You need to name the column in the output table (e.g. formatted_status: concat('Status: ', orders.status)). Only expression that has clear fully-qualified column name (e.g. orders.id) can be used directly and reference as-is in the output table.

Output

A table with the same number of rows as the input table, but with only the specified columns. E.g. select(orders, orders.id, formatted_status: concat('Status: ', orders.status) will return a table with 2 columns: orders.id and formatted_status.

orders.status	formatted_status
pending	Status: pending
cancelled	Status: cancelled
pending	Status: pending

Sample Usages

List all order items and their value

order_items | select(
  countries.name, 
  products.name,
  value: order_items.quantity * products.price
)

Open in AMQL playground

Result

SQL

SELECT
  "countries"."name" AS "countries->name",
  "products"."name" AS "products->name",
  ("order_items"."quantity" * "products"."price") AS "value"
FROM
  "demo"."order_items" "order_items"
  LEFT JOIN "demo"."orders" "orders" ON "order_items"."order_id" = "orders"."id"
  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"
  LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"

Use select to create an intermediate table for a metric

order_items | select(value: order_items.quantity * products.price) | sum(value)

Open in AMQL playground

Result

SQL

SELECT
  SUM(("order_items"."quantity" * "products"."price")) AS "sum_value"
FROM
  "demo"."order_items" "order_items"
  LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"