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

# Array & Map Functions

> Collection operations: SIZE, ELEMENT_AT, ARRAY_POSITION, FILTER, TRANSFORM, and more

## Array and map functions

### ARRAY

Creates an array from values.

```sql theme={null}
SELECT ARRAY('a', 'b', 'c') -- ['a', 'b', 'c']
SELECT ARRAY(1, 2, 3) AS numbers
```

### SIZE

Returns the number of elements in an array.

```sql theme={null}
SELECT SIZE(tags) AS tag_count FROM company_data."123"
```

### ELEMENT\_AT

Returns an element at a specific index (1-based) or map key.

```sql theme={null}
-- Array (1-based index)
SELECT ELEMENT_AT(ARRAY('a', 'b', 'c'), 2) -- 'b'

-- Map
SELECT ELEMENT_AT(properties, 'color') AS color FROM company_data."123"
```

### ARRAY\_FLATTEN

Flattens nested arrays into a single array.

```sql theme={null}
SELECT ARRAY_FLATTEN(ARRAY(ARRAY(1, 2), ARRAY(3, 4))) -- [1, 2, 3, 4]
```

### ARRAY\_SORT

Sorts array elements.

```sql theme={null}
SELECT ARRAY_SORT(ARRAY(3, 1, 2)) -- [1, 2, 3]
```

### ARRAY\_POSITION

Returns the 0-based index of the first occurrence of an element in an array. Returns `NULL` if the element is not found or if the search value is `NULL`.

**Syntax:** `ARRAY_POSITION(array, value)`

```sql theme={null}
-- Find the position of an element
SELECT ARRAY_POSITION(ARRAY('john', 'jack', 'jill'), 'jack') -- 1

-- Use with a dataset column
SELECT ARRAY_POSITION(tags, 'premium') AS premium_index
FROM company_data."123"

-- Returns NULL when not found
SELECT ARRAY_POSITION(ARRAY('a', 'b', 'c'), 'z') -- NULL
```

<Note>
  `ARRAY_POSITION` uses 0-based indexing, so the first element is at position `0`.
</Note>

### FILTER

Filters elements of an array using a lambda predicate. Returns a new array containing only elements for which the lambda returns `true`.

**Syntax:** `FILTER(array, element -> condition)`

```sql theme={null}
-- Keep only elements matching a condition
SELECT FILTER(tags, t -> t = 'active') AS active_tags
FROM company_data."123"

-- Filter with IN operator
SELECT FILTER(names, n -> n IN ('john', 'jack')) AS matched_names
FROM company_data."123"

-- Combine FILTER with other array functions
SELECT SIZE(FILTER(tags, t -> t = 'premium')) AS premium_count
FROM company_data."123"
```

### TRANSFORM

Applies a function to each element of an array, returning a new array of transformed values.

**Syntax:** `TRANSFORM(array, element -> expression)`

```sql theme={null}
-- Uppercase every element
SELECT TRANSFORM(names, n -> UPPER(n)) AS upper_names
FROM company_data."123"

-- Extract a field from an array of structs
SELECT TRANSFORM(items, x -> x."name") AS item_names
FROM company_data."123"

-- Chain FILTER and TRANSFORM together
SELECT TRANSFORM(FILTER(names, x -> x IN ('john', 'jack')), n -> UPPER(n)) AS result
FROM company_data."123"
```

### EXPLODE

Converts an array into multiple rows (use with LATERAL JOIN).

```sql theme={null}
SELECT t.user_id, tag
FROM company_data."123" t
LATERAL JOIN EXPLODE(t.tags) AS tag
```

### UNNEST

Expands an array into rows (alternative to EXPLODE).

```sql theme={null}
SELECT t.user_id, element
FROM company_data."123" t, UNNEST(t.tags) AS element
```

***

## Related content

<CardGroup cols={2}>
  <Card title="String Functions" icon="font" href="/nql/functions/string-functions">
    Text manipulation
  </Card>

  <Card title="Conditional Functions" icon="code-branch" href="/nql/functions/conditional-functions">
    IF, COALESCE, NULLIF
  </Card>

  <Card title="All Functions" icon="function" href="/nql/functions/index">
    Browse all function categories
  </Card>

  <Card title="Data Types" icon="shapes" href="/nql/data-types">
    ARRAY, MAP, and STRUCT type reference
  </Card>
</CardGroup>