# 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.

`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)
```

`{ARRAY}`

`{EXPRESSION}`

**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})
```

`{ARRAY}`

`{EXPRESSION}`

**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 first-n

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})
```

`{ARRAY}`

`{VALUE}`

`{AMOUNT}`

**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})
```

`{ARRAY}`

`{VALUE}`

`{AMOUNT}`

**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()
```

## Next steps

Now that you have learned about array, list, and set functions, you can use them within your PQL queries. For more information about other PQL functions, please read the Profile Query Language overview.