TOPICS×

# 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.
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 section.
```orders.topN(price, 5).head()

```