Announcing Advanced String and Aggregate Functions in FQL
Tags: serverless, graphql, database
Published: 2019-12-05 21:48:51

Author: Matt Freels & Lewis King
Date: October 18, 2019
[Originally posted on the Fauna blog.](https://fauna.com/blog/advanced-string-aggregate-functions-in-fql)

---

We’re pleased to announce new FQL capabilities that empower users to write more concise and powerful FQL statements with aggregate functions for sets (Count, Mean, Sum); string predicate functions (StartsWith, EndsWith, ContainsStr, ContainsStrRegex, RegexEscape); the MoveDatabase function; and the Now function.

Please note that these new functions are currently in Preview mode, which means that they will be fully supported in the JVM and JS drivers only and will continue to improve over the next 2 months. We invite you to send us feedback about their use. To learn more, please check out our [documentation](https://docs.fauna.com/).

#Aggregate functions for sets#
Following up on the recent addition of the Reduce() function, we have added new functions for common aggregations. Count(), Mean(), and Sum() functions all work on pages, arrays, and sets. Min() and Max() have also been enhanced to work on sets (as well as pages and arrays).

###Count() function *Preview*###
Count() can be used to return a count of elements in any set, array, or page.
```
Count(Match(Index("all_animals"))) //=> 30
```
###Mean() and Sum() functions *Preview*###
We have added a couple functions for computing the mean and sum of indexed numeric data.
```
Mean(Match("user_ages_by_name", "Alice")) //=> 43.75
```
```
Sum(Match("user_ages_by_name", "Bob")) //=> 1384
```
#String predicate functions#
We have added some additional functions which make it easier to search for specific strings with Filter() by returning true or false rather than an index or match structure.

###ContainsStr(), StartsWith(), and EndsWith() functions *Preview*###
These functions allow you to test if a search string contains a substring.

ContainsStr() returns true if the search string appears anywhere in the test string:
```
ContainsStr("Alice is 7 years old", "7") //=> true
```
StartsWith() and EndsWith() test if the search string is a prefix or suffix of the test string, respectively:
```
StartsWith("Alice is 7 years old", "Alice") //=> true
```
```
EndsWith("Alice is 7 years old", "old") //=> true
```
###ContainsStrRegex() function *Preview*###
This function allows you to check if a string matches a regex, which can be useful for more advanced predicates.

```
ContainsStrRegex("Alice is 7 years old", "\d+") //=> true
```
###RegexEscape() function *Preview*###
In order to be able to construct regular expressions from input data, it can be useful to escape the input, transforming it into a regex that matches the actual input value. RegexEscape() lets you do just that:
```
ContainsStrRegex("Alice has $10", RegexEscape("$10")) //=> true
```
#Even more functions!#
###MoveDatabase() function *Preview*###
MoveDatabase() provides the ability to move a database to another point in a database hierarchy, similar to the `mv` command for your file system. For example, say you are using databases to represent environments, and you move an app database from one to the other:
```
MoveDatabase(Database("appdb", Database("dev")), Database("prod"))
```
When moving a database, keys associated with the moved database move along with it, so previously distributed access keys continue to work. However, keys associated with the old parent may no longer be able to read or write into the moved database.

###Now() function *Preview*###
Previously, the only way to get the current time within a transaction (technically speaking, the transaction’s snapshot read time) was by passing the special argument "now" to Time(). Now we have a dedicated function for it, which we hope is easier to find if you are new to FQL.
```
Now() //=> Time("2019-10-18T09:00:00.000Z")
```
#Translating OpenCRUD predicates#
###Highlighting our users###
The request for many of these new functions comes from our friends at [GraphCMS](https://graphcms.com/), who helped identify additional areas for growth in our FQL API. Creating these new functions enables us to translate [OpenCRUD](https://www.opencrud.org/) predicates to queries on a single, field-sorted index, which was a request from GraphCMS that we were excited to fulfill.

Given the index below, which has no terms, and covers the “X” field and the document ref:
```
{   "name": "coll_by_x",  "source": Collection("a_collection"),  "terms": [],  "values": [{"field": ["data", "x"]}, {"field": "ref"}]}
```
The following are FQL representations of [OpenCRUD](https://github.com/opencrud/opencrud) single-field predicates:

###Strings###
**field_contains (contains string):**
```
Filter(Match("coll_by_x"), (x, r) => ContainsStr(x, <value>))
```
**field_starts_with (starts with string):**
```
Filter(Match("coll_by_x"), (x, r) => StartsWith(x, <value>))
```
**field_ends_with (ends with string):**
```
Filter(Match("coll_by_x"), (x, r) => EndsWith(x, <value>))
```

###Comparison###
**field_lte (less than):**
```
Range(Match("coll_by_x"), [], <value>)
```
**field_gte (greater than):**
```
Range(Match("coll_by_x"), <value>, [])
```
#Conclusion#
With our latest updates, users now have access to additional powerful regex and aggregate functionality.

These methods are in Preview! So please [let us know](https://twitter.com/fauna) what you think so that we can incorporate feedback into the formal release, and visit our [documentation](https://docs.fauna.com/) to learn more.

What other functions would you like to see implemented in FaunaDB? Please reach out to me on our [Community Slack](https://community-invite.fauna.com/) and describe any other features that would make FaunaDB an obvious choice for your next project.