# Array, list, and set functions

Profile Query Language (PQL) offers functions to make interaction with arrays, lists, and strings easier. More information about other PQL functions can be found in the Profile Query Language overview.

## In

The `in` function is used to determine if an item is a member of an array or list.

Format

``````{VALUE} in {ARRAY}
``````

Example

The following PQL query defines people with birthdays in March, June, or September.

``````person.birthMonth in [3, 6, 9]
``````

## Not in

The `notIn` function is used to determine if an item is not a member of an array or list.

NOTE

The `notIn` function also ensures that neither value is equal to null. Therefore, the results are not an exact negation of the `in` function.

Format

``````{VALUE} notIn {ARRAY}
``````

Example

The following PQL query defines people with birthdays that are not in March, June, or September.

``````person.birthMonth notIn [3, 6, 9]
``````

## Intersects

The `intersects` function is used to determine if two arrays or lists have at least one common member.

Format

``````{ARRAY}.intersects({ARRAY})
``````

Example

The following PQL query defines people whose favorite colors include at least one of red, blue, or green.

``````person.favoriteColors.intersects(["red", "blue", "green"])
``````

## Intersection

The `intersection` function is used to determine the common members of two arrays or lists.

Format

``````{ARRAY}.intersection({ARRAY})
``````

Example

The following PQL query defines if person 1 and person 2 both have favorite colors of red, blue, and green.

``````person1.favoriteColors.intersection(person2.favoriteColors) = ["red", "blue", "green"]
``````

## Subset of

The `subsetOf` function is used to determine if a specific array (array A) is a subset of another array (array B). In other words, that all elements in array A are elements of array B.

Format

``````{ARRAY}.subsetOf({ARRAY})
``````

Example

The following PQL query defines people who have visited all of their favorite cities.

``````person.favoriteCities.subsetOf(person.visitedCities)
``````

## Superset of

The `supersetOf` function is used to determine if a specific array (array A) is a superset of another array (array B). In other words, that array A contains all elements in array B.

Format

``````{ARRAY}.supersetOf({ARRAY})
``````

Example

The following PQL query defines people who have eaten sushi and pizza at least once.

``````person.eatenFoods.supersetOf(["sushi", "pizza"])
``````

## Includes

The `includes` function is used to determine if an array or list contains a given item.

Format

``````{ARRAY}.includes({ITEM})
``````

Example

The following PQL query defines people whose favorite color includes red.

``````person.favoriteColors.includes("red")
``````

## Distinct

The `distinct` function is used to remove duplicate values from an array or list.

Format

``````{ARRAY}.distinct()
``````

Example

The following PQL query specifies people who have placed orders in more than one store.

``````person.orders.storeId.distinct().count() > 1
``````

## Group by

The `groupBy` function is used to partition values of an array or list into a group based on the value of the expression.

Format

``````{ARRAY}.groupBy({EXPRESSION)
``````
Argument Description
`{ARRAY}` The array or list that is to be grouped.
`{EXPRESSION}` An expression which maps each item in the array or list returned.

Example

The following PQL query groups all the orders by which store the order was placed at.

``````orders.groupBy(storeId)
``````

## Filter

The `filter` function is used to filter an array or list based on an expression.

Format

``````{ARRAY}.filter({EXPRESSION})
``````
Argument Description
`{ARRAY}` The array or list that is to be filtered.
`{EXPRESSION}` An expression to filter by.

Example

The following PQL query defines all people who are 21 or older.

``````person.filter(age >= 21)
``````

## Map

The `map` function is used to create a new array by applying an expression to each item in a given array.

Format

``````array.map(expression)
``````

Example

The following PQL query creates a new array of numbers and squares the value of the original numbers.

``````numbers.map(square)
``````

## First `n` in array

The `topN` function is used to return the first `N` items in an array, when sorted in ascending order based on the given numerical expression.

Format

``````{ARRAY}.topN({VALUE}, {AMOUNT})
``````
Argument Description
`{ARRAY}` The array or list that is to be sorted.
`{VALUE}` The property in which to sort the array or list.
`{AMOUNT}` The number of items to be returned.

Example

The following PQL query returns the top five orders with the highest price.

``````orders.topN(price, 5)
``````

## Last `n` in array

The `bottomN` function is used to return the last `N` items in an array, when sorted in ascending order based on the given numerical expression.

Format

``````{ARRAY}.bottomN({VALUE}, {AMOUNT})
``````
Argument Description
`{ARRAY}` The array or list that is to be sorted.
`{VALUE}` The property in which to sort the array or list.
`{AMOUNT}` The number of items to be returned.

Example

The following PQL query returns the top five orders with the lowest price.

``````orders.bottomN(price, 5)
``````

## First item

The `head` function is used to return the first item in the array or list.

Format

``````{ARRAY}.head()
``````

Example

The following PQL query returns the first of the top five orders with the highest price. More information about the `topN` function can be found in the first `n` in array section.

``````orders.topN(price, 5).head()
``````