Arrays

Arrays are used to store and manipulate lists of items - for example, a list of contacts, invoice line items, or a list of numbers.

Filtering with WHERE

Use WHERE to keep only the items you need. Inside the query, names like price and name refer to fields on the current item.

Given a list of invoice line items where each row has price, quantity, name, and category:

Filter by a value: Line Items WHERE price > 10
Combine conditions: Line Items WHERE price > 10 AND quantity > 2
Compare to another variable: Line Items WHERE price > Minimum Price

Picking fields with SELECT

By default, the query returns the full matching items. Using SELECT, you can get a specific field from each item.

Imagine a contact list where each item has name and status:

Select a specific field: Contacts SELECT name
Combine with WHERE: Contacts WHERE status = "active" SELECT name

If a field does not exist on an item, the result is empty for that item.

Sorting with ORDER BY

Use ORDER BY to sort the results. By default, sorting goes from lowest to highest (ascending order, ASC). Use DESC for highest to lowest (descending order). Empty values are always last.

Given a list of line items with price, quantity, name, and category:

Lowest to highest: Line Items ORDER BY price ASC
Highest to lowest: Line Items WHERE price > 10 ORDER BY price DESC
Sort by multiple fields: Line Items ORDER BY category ASC, price DESC

Referring to the current item

Inside a query, there are a few ways to refer to the item being processed.

Imagine the following list of contacts with name, department, and status:

Direct field names

The simplest approach - use the field name directly:

Contacts WHERE department = "Sales" SELECT name

Field names are case-sensitive (Price and price are different).

Special `@item` variable

Every query has an automatic special variable called @item that refers to the current item. You can use this variable when you want to refer to the whole item explicitly.

For simple lists, like a list of numbers or text values, @item is the value itself. For items with multiple fields, @item works the same as a field name when followed by a dot:

Contacts WHERE @item -> department = "Sales" SELECT @item -> name

Custom alias with AS

You can choose to give each item a short name (alias) to make it easier to read:

Contacts AS person WHERE person.status = "active" SELECT person.name

Using with functions

Given the examples above, array queries work anywhere a formula expects a list.

Total of matching prices: SUM(Line Items WHERE price > 10 SELECT price)
First matching contact: FIRST(Contacts WHERE status = "active" ORDER BY name ASC)
Last matching contact: LAST(Contacts WHERE status = "active" ORDER BY name ASC)
Count matching rows: LEN(Contacts WHERE status = "active")
Name from the most expensive line item: GET(FIRST(Line Items WHERE price > 10 ORDER BY price DESC), "name")