# array_agg

Creates an array of the argument values. The array retains `NULL` elements; use `FILTER` to exclude them.

Specify an aggregate `ORDER BY` when the order of elements matters. Without it, the element order follows the rows admitted by the query and should not be relied on.

**Syntax**

```text
array_agg(value [ORDER BY expression])
```

**Arguments**

* `value` — A column or expression. The element type follows native collection type inference; nullable members are retained.

**Returned value**

A native [`Array`](https://coordinatordb.com/docs/reference/data-types/array) containing one element per admitted row. No input rows returns SQL `NULL`, rather than an empty array.

**Examples**

**Collecting names by department**

```sql
CREATE TABLE employees (department TEXT, name TEXT);
INSERT INTO employees VALUES ('Engineering', 'Ada'), ('Engineering', 'Grace'), ('Sales', 'Sam');
```

```sql
SELECT department, array_agg(name ORDER BY name) AS names
FROM employees
GROUP BY department
ORDER BY department
FORMAT csv;
```

```text
"Engineering","[""Ada"",""Grace""]"
"Sales","[""Sam""]"
```

**Keeping or removing NULL elements**

```sql
CREATE TABLE readings (value INTEGER);
INSERT INTO readings VALUES (10), (NULL), (20);
```

```sql
SELECT array_agg(value ORDER BY value NULLS LAST),
       array_agg(value ORDER BY value) FILTER (WHERE value IS NOT NULL)
FROM readings
FORMAT csv;
```

```text
"[10,20,null]","[10,20]"
```

**Collecting distinct values**

```sql
CREATE TABLE tags (value TEXT);
INSERT INTO tags VALUES ('sql'), ('rust'), ('sql');
```

```sql
SELECT array_agg(DISTINCT value ORDER BY value)
FROM tags
FORMAT csv;
```

```text
"[""rust"",""sql""]"
```

See [array functions](https://coordinatordb.com/docs/reference/functions/regular-functions/array-functions) for reading and transforming the result.