Holistics AQL learning Lesson 0011

AQLearn 2.2 / Syntax

Pipe (1)

Source lesson: AQLearn 2.2: Pipe (1). Tangible win: read any AQL pipeline left to right and name the input passed at each |.

Purpose of this lesson

Have you ever tried to explain "what does products | count(products.id) actually do" to a teammate or a customer in a presales call?

You hit these pain points:

  1. The pipe character | does not exist in SQL, so people new to AQL freeze and stop reading.
  2. You guess "it is like SQL FROM", but cannot defend the rule when the next step adds another |.
  3. You compose longer metrics later (count | where | of_all) and lose track of which step receives which input.

This lesson solves it: define the one rule that controls every pipe in AQL, draw the data flow visually, and map it to the SQL clauses you already know.

Why this matters for your mission: every metric expression in AQL is a pipeline. Once you can read pipes, every later concept (filter, where, of_all, nested aggregations) is just another step in the chain.

One rule, in one sentence

The pipe takes the value on its left and uses it as the input of the function on its right.

left | function(args)
// same as
function(left, args)

That is the entire rule. Everything else in this lesson is just practicing it.

Visualize one step

Input (table) products all rows of the products model | Function (aggregate) count(products.id) count the values of products.id across the input rows returns Output (scalar) 19 a single number
products | count(products.id): products go in, the count function consumes them, a single number comes out.

SQL translation

The pipe is the AQL way to say "this is the data this function operates on". In SQL the same idea is split between the FROM clause and the aggregate function call.

AQLSQL
products | count(products.id)select count(products.id) from products
orders | max(orders.created_at)select max(orders.created_at) from orders
order_items | sum(order_items.quantity)select sum(order_items.quantity) from order_items

In AQL the input model on the left of | is what SQL hides inside the FROM. Putting the input on the left also makes longer pipelines readable: you literally read what happens to the data, left to right.

What can sit on each side

Left of |

Anything that produces data: a model name, another pipeline, a previously defined metric, or a value.

products
orders | filter(orders.status == 'delivered')
my_metric

Right of |

A function that knows how to consume that input. Aggregate functions, filter, where, group, select, of_all, and more.

count(products.id)
filter(orders.status == 'delivered')
where(users.gender == 'F')

Chaining: many pipes in a row

You can keep piping. Each | hands the previous step's output to the next function.

orders
  | filter(orders.status == 'delivered')
  | count(orders.id)
Step 1: input table orders all rows of orders | Step 2: narrower table filter(status == 'delivered') only delivered orders | Step 3: scalar count(orders.id) a single number
Each pipe hands its output to the next step. Read left to right: start from a table, narrow it, then aggregate.

Why the pipe form is worth the syntax

Customer-safe explanation: "The vertical bar means 'use this as the input for what comes next'. Start with a table, pass it to a filter, pass that to a count. Reads like a pipeline."

Dataset diagram

The pipe operator does not change the dataset shape; it only shapes how a calculation is written. The AQLearn starter for this lesson queries products alongside orders and order_items.

N:1 N:1 N:1 N:1 N:1 countries codetext, pk nametext continent_nametext cities idnumber, pk nametext country_codetext, fk users idnumber, pk nametext gendertext agenumber sign_up_atdatetime city_idnumber, fk orders pipe input idnumber, pk statustext created_atdatetime delivery_attemptsnumber discountnumber user_idnumber, fk order_items order_idnumber, fk product_idnumber, fk quantitynumber products pipe input idnumber, pk nametext pricenumber category_idnumber, fk merchant_idnumber, fk
Either products or orders can sit on the left of a pipe and be the input for an aggregate function on the right.
primary key foreign key

Retrieval practice

1. The pipe operator does what?
2. In SQL, the AQL input on the left of a pipe maps most directly to which clause?
3. products | count(products.id) returns...
4. To read orders | filter(...) | count(...) you should...

Do in AQLearn

  1. Open AQLearn Pipe (1).
  2. Run the starter expression. Note that the metric body is just products with no aggregate, so the value is a row count of the source.
  3. Change the metric body to products | count(products.id) and re-run.
  4. Switch the input model from products to orders and read the result aloud as "orders, piped into count of orders.id".

Next local lesson

Next: Table vs Scalar outputs. The two shapes AQL expressions can return and how the pipe decides which functions are valid.