> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for ARRAY_AGG function

# ARRAY_AGG

Concatenates input values, including `NULL` values, into an array.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ARRAY_AGG(<expression>) [FILTER ([WHERE] <condition>)]
```

## Parameters

| Parameter      | Description                                                       | Supported input type |
| :------------- | :---------------------------------------------------------------- | :------------------- |
| `<expression>` | Expression of any type to be accumulated into an array.           | Any                  |
| `<condition>`  | An optional boolean expression to filter rows used in aggregation | `BOOL`               |

## Return Type

`ARRAY` of the same type as the input data. If there is no input data, `ARRAY_AGG` returns `NULL`.

## Example

The following examples use a `player_information` table:

<div className="query-window">
  ```
  SELECT nickname, playerid
  FROM (VALUES
      ('stephen70', 1),
      ('burchdenise', 7),
      ('sabrina21', NULL)
  ) AS player_information(nickname, playerid);
  ```

  | nickname <span>text</span> | playerid <span>int null</span> |
  | :------------------------- | :----------------------------- |
  | stephen70                  | 1                              |
  | burchdenise                | 7                              |
  | sabrina21                  | NULL                           |

  <p><span>Rows: 3</span><span>Execution time: 5ms</span></p>
</div>

The following example aggregates the columns into arrays, including `NULL` values:

<div className="query-window">
  ```
  SELECT
      ARRAY_AGG(nickname) AS nicknames,
      ARRAY_AGG(playerid) AS playerids
  FROM
      (VALUES
          ('stephen70', 1),
          ('burchdenise', 7),
          ('sabrina21', NULL)
      ) AS t(nickname, playerid);
  ```

  | nicknames <span>array(text) null</span>    | playerids <span>array(int null) null</span> |
  | :----------------------------------------- | :------------------------------------------ |
  | \['stephen70', 'burchdenise', 'sabrina21'] | \[1, 7, NULL]                               |

  <p><span>Rows: 1</span><span>Execution time: 10.40ms</span></p>
</div>

The following example shows that when a `WHERE` filter rejects all rows, `ARRAY_AGG` returns `NULL`:

<div className="query-window">
  ```
  SELECT
      ARRAY_AGG(nickname) AS nicknames,
      ARRAY_AGG(playerid) AS playerids
  FROM
      (VALUES
          ('stephen70', 1),
          ('burchdenise', 7),
          ('sabrina21', NULL)
      ) AS t(nickname, playerid)
  WHERE
      playerid = 42;
  ```

  | nicknames <span>array(text) null</span> | playerids <span>array(int) null</span> |
  | :-------------------------------------- | :------------------------------------- |
  | NULL                                    | NULL                                   |

  <p><span>Rows: 1</span><span>Execution time: 8.85ms</span></p>
</div>