Queries

A FlowScript sequence value represents an ordered list of elements. This document describes how to write query expressions to filter, order, group, join and modify such sequences.

Remember that all values in FlowScript are immutable. You cannot change a sequence value; you can only make modified copies of it.

Advanced: A note on laziness

All the query operators described in this document express lazy sequences. This means that an expression such as users where name == "joel" does not perform the work of filtering the sequence immediately. Instead, the filtering happens on-demand whenever the elements of the sequence are needed.

This has several benefits:

• Queries can be efficiently composed: you can filter or transform a sequence in several steps without incurring the performance penalty of creating the intermediate sequences.

• An expression such as first(users where active == 1) becomes much more efficient, as the filtering operation can stop when the first active user is found.

However, it laziness can also degrade performance in situations where the elements of a sequence are enumerated more than once, such as:

• looping over a queried sequence several times, or

• calling functions such as first or last on a queries sequence several times.

In those cases, you can use the eval function to materialize the sequence.

// Example: 'activeUsers' is a lazy sequence,
// so it is more efficient to materialize it using eval
// before accessing its first and last elements.

let activeUsers = eval(users where active == 1);
let firstActiveUser = first(activeUsers);
let lastActiveUser = last(activeUsers);

Filtering

To filter a sequence of records, use the where operator. This operator takes a sequence expression on the left and a filter expression on the right. The resulting values contains only the records for which the filter expression evaluates to true.

let users = [
    { name: "yohannes", active: 1 },
    { name: "mira", active: 0 },
    { name: "sosuke", active: 1 }
];

let activeUsers = users where active == 1;

As demonstrated in the example above, the filter expression has access to each member in the sequence's records as if they were local variables. The expression active == 1, denoting a boolean value indicating whether a user's "active" flag is equal to one, is written from a perspective "inside" a record.

It is also possible to use the where operator with an alias.

Ordering

To order a sequence of records, use the order by operator, which takes a sequence expression on the left and a comma-separated list of sort specifiers on the right. In the example below, the countries are sorted by population, descending order, and secondarily by name in ascending order (making sure the Netherlands and Zambia, having the same population size in this example, come in alphabetical order).

let countries = [
    { name: "Sweden", populationInMillions: 10 },
    { name: "Zambia", populationInMillions: 19 },
    { name: "Netherlands", populationInMillions: 19 },
    { name: "United States", populationInMillions: 331 },
    { name: "China", populationInMillions: 1441 },
    { name: "Brazil", populationInMillions: 213 }
];

return countries order by populationInMillions desc, name asc

Select queries

FlowScript supports an SQL-like query syntax for sequences of records. The example below queries a sequence of countries, transforming the populationInMillions field of each record into a member labelled "population", ordering the result by the new population member in ascending order.

let countries = [
    { name: "Nigeria", populationInMillions: 211 },
    { name: "Japan", populationInMillions: 126 },
    { name: "Germany", populationInMillions: 83 },
    { name: "Mexico", populationInMillions: 126 },
    { name: "Egypt", populationInMillions: 104 }
];

return select name,
              populationInMillions * pow(10, 6) as population
       from countries
       order by population asc;

Select queries can select, filter, group, join and sort records using the select, where, group by, join and order by clauses. Only the select clause is mandatory.

The select clause

The select clause specifies the format of the query result. A select query starts with the keyword select followed by a comma-separated list of expressions giving the members to include, the from keyword, and a source expression. If the wildcard (*) symbol is included in the list of member expressions, all members of the source table are included:

return select * from countries;

A member expression may be implicitly or explicitly labelled. Complex expressions (including more than one identifier) must be explicitly labelled. Explicitly labelled members are declared using the as keyword. In the following example, the "name" member is implicitly labelled and will have the label "name" in the resulting sequence of records. The second member is explicitly labelled "population". Because there is no wildcard symbol, the populationInMillions member will not be a part of the result.

return select name,
              populationInMillions * pow(10, 6) as population
       from countries;

If the select keyword is followed by the distinct keyword, only distinct records are included in the result:

return select distinct * from [{x: 1}, {x: 1}, {x: 2}];

The where clause

The where clause filters the result set, much like the standalone where keyword:

let shapes = [
    { name: "Square", numberOfLines: 4 },
    { name: "Triangle", numberOfLines: 3 },
    { name: "Pentagon", numberOfLines: 5 },
    { name: "Hexagon", numberOfLines: 6 },
    { name: "Circle", numberOfLines: 1 }
];

return select * from shapes where numberOfLines > 1;

The group by clause

The group by clause is used to group rows that share some values into summary rows, typically in combination with aggregation functions like sum, count, etc.

The following example takes a list of sales transactions and groups them by the sold product, summing the quantity and price for each product. It also includes the customerID members without any grouping.

let salesTransactions = [
    { id: 1, customerID: 101, product: "Laptop", quantity: 2, price: 2000 },
    { id: 2, customerID: 102, product: "Smartphone", quantity: 1, price: 800 },
    { id: 3, customerID: 101, product: "Tablet", quantity: 3, price: 1500 },
    { id: 4, customerID: 103, product: "Laptop", quantity: 1, price: 1000 },
    { id: 5, customerID: 104, product: "Smartphone", quantity: 2, price: 1600 },
    { id: 6, customerID: 104, product: "Tablet", quantity: 2, price: 1000 }
];

let perProduct = select product,
                        sum(quantity) as quantity, 
                        sum(price) as price,
                        customerID as customerIDs
                 from salesTransactions
                 group by product
                 
return perProduct;

The join clause

Join clauses are used to combine records from two sequences based one some relationship between the records. In the example below, each sales transaction is paired with the name of the corresponding customer. The last transaction (with ID 6) has no curresponding customer, so its customer name will be null.

let salesTransactions = [
    { id: 5, customerId: 105, product: "Smartphone", quantity: 2, totalPrice: 1600 },
    { id: 2, customerId: 102, product: "Smartphone", quantity: 1, totalPrice: 800 },
    { id: 4, customerId: 104, product: "Laptop", quantity: 1, totalPrice: 1000 },
    { id: 1, customerId: 105, product: "Laptop", quantity: 2, totalPrice: 2000 },
    { id: 3, customerId: 103, product: "Tablet", quantity: 3, totalPrice: 1500 },
    { id: 6, customerId: 106, product: "Tablet", quantity: 2, totalPrice: 1000 }
];

let customers = [
    { customerId: 101, name: "Sakura", email: "[e-mail]" },
    { customerId: 102, name: "Ahmed", email: "[e-mail]" },
    { customerId: 103, name: "Elena", email: "[e-mail]" },
    { customerId: 104, name: "Juan", email: "[e-mail]" },
    { customerId: 105, name: "Ling", email: "[e-mail]" }
];


return select t.id, t.product, c.name as customerName, t.customerId
       from salesTransactions as t
       left join customers as c on c.customerId = t.customerId

Left join

Left join will return all elements from the left sequence and all matching records in the right sequence. Where no matching element is found on the right, fields selected from the right will be null.

let customers= [
    { customerId: 0, name: "Ramona" },
    { customerId: 1, name: "Yusuf" }
    { customerId: 2, name: "Mei", }
];

let orders = [ 
    {id: 3, customerId: 0, product: "Laptop", quantity: 2, totalPrice: 1000}
]


return select name, IsNull(product, "-") 
       from customer left join orders on customerId = id;
/*  will return 
    [ 
        { name: "Ramona", product: "Laptop"}, 
        { name: "Yusuf", product: "-"}, 
        { name: "Mei", product: "-"}
    ] 
*/

Right join

A mirror image of the left join, right join will return all elements from the right sequence and all matching records in the left sequence. Where no matching element is found on the left, fields selected from the left will be null.

let customers= [
    { customerId: 0, name: "Dominik" },
    { customerId: 1, name: "Omar" }
    { customerId: 2, name: "Harper", }
];

let orders = [ 
    {id: 1, customerId: 0, product: "Laptop", quantity: 2, totalPrice: 1000},
    {id: 2, customerId: 5, product: "Tablet", quantity: 3, totalPrice: 1500}
]


return select IsNull(name, "-"), product
       from customer right join orders on customerId = id;
/*  will return 
    [ 
        { name: "Dominik", product: "Laptop"}, 
        { name: "-", product: "Tablet"}
    ] 
*/

Inner join

Inner join will return all of the matching pair of elements from both of the sequences.

let customers= [
    { customerId: 0, name: "Michaela" },
    { customerId: 1, name: "Jakub" },
    { customerId: 2, name: "Hugo", }
];

let orders = [ 
    {id: 1, customerId: 0, product: "Laptop", quantity: 2, totalPrice: 1000},
    {id: 2, customerId: 0, product: "Tablet", quantity: 5, totalPrice: 1500}
]


return select name, product 
       from customer inner join orders on customerId = id;
/*  will return 
    [ 
        { name: "Michaela", product: "Laptop"}, 
        { name: "Michaela", product: "Tablet"}
    ] 
*/

Outer join

Outer join will return all elements from both of the sequences in the select join clause, adding null on all fields for non-matching elements.

let customers= [
    { customerId: 0, name: "Amaya" },
    { customerId: 1, name: "Rohan" },
    { customerId: 2, name: "Kristofer", }
];

let orders = [ 
    {id: 1, customerId: 0, product: "Laptop", quantity: 2, totalPrice: 1000},
    {id: 2, customerId: 3, product: "Tablet", quantity: 5, totalPrice: 1500}
]

return select isNull(name, "-"), isNull(product, "-") 
       from customer outer join orders on customerId = id;
/*  will return 
    [        
        { name: "Amaya", product: "Laptop"},
        { name: "Rohan", product: "-"}, 
        { name: "Kristofer", product: "-"}
        { name: "-", product: "Tablet"},
    ] 
*/

Aliases

All the different query types support aliases, which is useful if the records in the queries sequence contain some member whose label is the same as the name of some variable.

let id = 1;
let users = [
    { id: 0, name: "Jim" },
    { id: 1, name: "Joe" }
];

return first(users as u where u.id = id);

Advanced: Functional-style queries

The different types of query syntax described above are well suited for table-like sequences of records. It is less well suited for nested structures, sequences of simple values or sequences of sequences. For these cases, you can use functional-style queries using functions defined in the Seq module.

In the example below, each customer record contains its own list of transactions. A functional-style query is used to find the sum of the total price of all transactions for all customers.

open Seq;

let customers = [
    {
        customerId: 101, 
        name: "Aaron", 
        email: "[e-mail]",
        transactions: [
            { id: 9, product: "Smartphone", quantity: 1, totalPrice: 800 }
        ]
    },
    {
        customerId: 102, 
        name: "Felicia", 
        email: "[e-mail]",
        transactions: [
            { id: 10, product: "Tablet", quantity: 2, totalPrice: 1500 },
            { id: 11, product: "Laptop", quantity: 1, totalPrice: 200 }
        ]
    },
];

return customers.collect(c => c.transactions) // get all customer transactions
                .map(t => t.totalPrice) // get the total price for each transaction
                .sum(); // sum the sequence of numbers