Collections

The collection step is made of a data set (collection) and a set of formats that can pick data from the table. The collection can be many megabytes in size and you can define hundreds formats.

Example Collection

Example Formats

'collection.map(age)' // [30, 24, 40]
'collection.map(age).first()' // 30
'collection.map(age).last()' // 40
'collection.filter(age<30).map(age).first()' // 24
'collection.filter(is_male=true).filter(age>30).map(name)first()' // jack

The output must resolve to a value (e.g. number, string, boolean, date). Collection queries must start with the word 'collection'.

Using inputs from previous steps

The input value is wrapped in {{ }}.

For example, if the input is call customer_age and is equal to 30 in the quote, then:

'collection.filter(age<{{customer_age}}).map(age).first()' // 24

// resolves to

'collection.filter(age<30).map(age).first()' // 24

Methods

- count()

Counts the number of items in an response. It can be used after a filter has been applied.

Example:

'collection.count()' // 3
'collection.filter(age<30).count()' // 1
'collection.filter(is_male=true).filter(age>25).count()' // 2

- min(), max(), mean(), range(), sum()

Finding the min number, max number, mean number, sum number and range between the two.

Example:

'collection.map(age).min()' // 24
'collection.map(age).max()' // 40
'collection.map(age).range()' // 16 i.e. 40 - 24
'collection.map(age).mean()' // 31.3333333333
'round(collection.map(age).mean(), 2)' // 31.33
'collection.map(age).sum()' // 94

- map()

Returns an array of the selected column within a collection of items. You can not return an array but you can have it as a chained step to do work on a collection.

Example:

'collection.map(age)' // [30, 24, 40]
'collection.map(name)' // ["bob", "sue", "jack"]

// You must resolve the array to make an output

'collection.map(name).first()' // "bob"

- filter()

Returns a filtered collection based on a columns criteria.

Filters will work on columns with strings, booleans and numbers. Filters can be chained.

You can also filter against value or empty/null data with the ~ flag

Example:


'collection.filter(age>30).map(name)' // ["jack"]
'collection.filter(age<40).filter(is_male=true).map(name)' // ["bob"]
'collection.filter(age<40).filter(age>24).map(name)' // ["bob"]
'collection.filter(surname=brown~).count()' // 3 as the ~ returns empty cells too

// You must resolve the array to make an output
'collection.filter(age>30).map(name).first()' // "jack"

- unique

Returns an array of the unique items from a column

Example:

'collection.unique(age).count()' // 3
'collection.unique(age).map(age).sum()' // 94
'collection.unique(is_male).count()' // 2

- first, last

Returns an first or last element of an array as a value. This is a common way to resolve a result.

Example:

'collection.map(age).first()' // 30
'collection.map(age).last()' // 40
'collection.filter(age<30).map(age).first()' // 24

- exists

Checks if property value exists i.e. not null, undefined, empty string or 0.

Example:

'collection.filter(age>50).exists()' // "false"
'collection.filter(age<50).exists()' // "true"

- date, age

If property is a date, then you can use date() to find a hour, date, month or year from the date string, or use age() to find the age since now in hours, days, months or years.

You can format a date from US or UK date formats.

Example:

'collection.map(dob).first().date(YY)' // 1993
'collection.map(dob).first().date(MM)' // 12
'collection.map(dob).first().date(DD)' // 12
'collection.map(dob_format).first().date(UK)' // 1993-12-12
'collection.map(dob_format).first().date(US)' // 1993-12-12

'collection.map(dob).first().age(YY)' // 30
'collection.map(dob).first().age(MM)' // 360
'collection.map(dob).first().age(DD)' // 131800

A second argument can be provided to point at a date in the object instead of using now.

'collection.map(dob).first().age(YY, "2020-01-01")' // 27

Date and age work with arrays.

'collection.map(dob).age(YY)' // [ 30 , 24, 40 ]

- postcode

If property is a string postcode, then you can find the area and sector.

Example:

'collection.map(postcode).first().postcode(area)'); // "SW"
'collection.map(postcode).first().postcode(sector)'); // "SW1A"
'collection.map(postcode).postcode(area)'); // ["SW", "L", "M"]

- regex

If property is a string, then you can use regex() to parse a regular expression against the string and return the first match.

Example:

'collection.map(postcode).first().regex(^[A-Za-z]{2}|^[A-Za-z]{1})'); // W
'collection.map(postcode).first().regex(^[A-Za-z]{2}|^[A-Za-z]{1})'); // PO

Last updated