Table queries

JOIN

The Join function concatenates all values of a table column into a single textual value with a given separator.

In the example below, the orderNo column of the orders table is aggregated into a comma-separated string in the format <orderNo 1>, <orderNo 2>, <orderNo 3> etc.

let orderNumbers = join(orders.orderNo, ', ');

MAP-AS

The Map-As construct is used to create a new table variable based on an existing table variable, changing the column names and adding or removing new columns. The syntax of the map-as construct is MAP <source table> AS <record literal>. The record literal to the right of the AS keyword has the scope of the table.

The example below shows an orderLines table being transformed, keeping the salesPart column but adding a new column called cost, which is calculated using the price and discountPercent columns of the orderLines table.

let newOrderLines = map orderLines as [salesPart, cost: price * discount];

ORDER BY

ORDER BY sorts a table by one column and returns a new sorted table. As default, the order is ascending but by adding the keyword DESC the order can be changed to descending (ASC can be added explicitly when sorting ascending). Only simple types can be used for sorting.

let items = [id: 1, price: 10, name: 'Soda'] & 
            [id: 2, price: 100, name: 'Burger'] & 
            [id: 3, price: 50, name: 'Fries'];

let cheapItem = First(items order by price);
let expensiveItem = First(items order by price desc);

SELECT / DISTINCT

It is possible to express something like SQL DISTINCT in FlowScript queries. The syntax is directly taken from SQL:

select distinct x, y
from myTable

This would return all the distinct combinations of x and y in myTable. For a single-column selection (e.g. select distinct x from myTable), a table containing all unique x would be returned.

Just like you'd expect, the expression allows for where filtering:

select distinct x, y
from myTable 
where z = 0

...which is to be understood as equal to:

select distinct x, y
from (myTable where z = 0)

It is possible to omit the "distinct" keyword, turning the select-expression into a normal table projection. It is also possible to use expressions in the column projection:

select x + 1, y + 1
from myTable

If such an expression contains more than one identifier (variable name, function name, type name, etc) then they need to be aliased using the AS keyword:

select x + y as result
from myTable

It is possible to use the wildcard * sign alone or together with column expressions. If the wildcard is present, all columns not explicitly present in the selection are also included, unmodified, in the output.

select *, x + y as result
from myTable

The syntax for select (using pseudo-grammar) is:

Selection = "*" | (Expression + ("AS" + Identifier)?)

ListOfSelections = Selection + ("," + Selection)*

SelectExpression = "SELECT" + "DISTINCT" + ListOfSelections + "FROM" + Expression

WHERE

You can use the WHERE keyword to create a filtered version of a table variable. Since this filtering happens locally on the device where the Flow client is run, it is not suitable for large amounts of data.

The WHERE keyword takes a table on the left-hand side and a logical expression (with the scope of the table's columns) on the right-hand side.

Since the WHERE keyword returns a new table, you can nest it in any table function, such as First, Any, Count, etc.

let orders = [id: 1, isConfirmed: 0, plannedDate: Now()] & 
             [id: 2, isConfirmed: 1, plannedDate: Now() + 1] & 
             [id: 3, isConfirmed: 0, plannedDate: Now() + 2];
let futureOrders = orders where isConfirmed and plannedDate > Now();
let nextOrderToProcess = first(orders where isConfirmed);
let numberOfOrder = count(orders where isConfirmed);