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

# Filtering and Transforming Data

> Select columns, filter rows, and transform data with NQL

This guide covers the fundamentals of querying data with NQL: selecting columns, filtering rows with WHERE clauses, and transforming data using expressions and functions.

## Prerequisites

* A Narrative I/O account with at least one dataset
* Basic familiarity with SQL syntax

## What you'll learn

* How to select specific columns and use aliases
* How to filter data with comparison and logical operators
* How to transform data using expressions and functions
* How to work with complex types (arrays, structs, maps)

***

## Selecting columns

### Basic column selection

Select specific columns from a dataset:

```sql theme={null}
SELECT
  user_id,
  email,
  created_at
FROM company_data."123"
```

### Using column aliases

Rename columns in your output with `AS`:

```sql theme={null}
SELECT
  user_id AS id,
  email AS contact_email,
  created_at AS signup_date
FROM company_data."123"
```

### Fully qualified column names

When joining datasets or for clarity, use fully qualified names:

```sql theme={null}
SELECT
  company_data."123".user_id,
  company_data."123".email
FROM company_data."123"
```

<Tip>
  Always use fully qualified column names in joins to avoid ambiguity.
</Tip>

<Note>
  NQL requires explicit column lists. Wildcards (`SELECT *`) are not supported. See [Explicit Column Selection](/nql/general/explicit-columns) for why and how to list columns explicitly.
</Note>

### Select distinct values

Remove duplicates with `DISTINCT`:

```sql theme={null}
SELECT DISTINCT category
FROM company_data."123"
```

***

## Filtering with WHERE

### Comparison filters

Filter rows using comparison operators:

```sql theme={null}
SELECT user_id, email, age, status
FROM company_data."123"
WHERE
  age >= 18
  AND age < 65
  AND status = 'active'
```

| Operator | Meaning               |
| -------- | --------------------- |
| `=`      | Equal to              |
| `<>`     | Not equal to          |
| `<`      | Less than             |
| `>`      | Greater than          |
| `<=`     | Less than or equal    |
| `>=`     | Greater than or equal |

### Combining conditions

Use `AND`, `OR`, and `NOT` to combine conditions:

```sql theme={null}
SELECT user_id, email, status, region
FROM company_data."123"
WHERE
  (status = 'active' OR status = 'pending')
  AND NOT is_test_account
  AND region = 'US'
```

Use parentheses to control evaluation order. `AND` is evaluated before `OR`.

### IN and NOT IN

Check membership in a list:

```sql theme={null}
SELECT user_id, category, source, created_at
FROM company_data."123"
WHERE
  category IN ('electronics', 'clothing', 'home')
  AND source NOT IN ('test', 'internal')
```

### BETWEEN

Filter within a range (inclusive):

```sql theme={null}
SELECT user_id, email, age, score
FROM company_data."123"
WHERE
  age BETWEEN 25 AND 45
  AND score BETWEEN 80 AND 100
```

### NULL checks

Check for missing values:

```sql theme={null}
SELECT user_id, email, phone
FROM company_data."123"
WHERE
  email IS NOT NULL
  AND phone IS NOT NULL
  AND secondary_email IS NULL
```

<Warning>
  Don't use `= NULL` or `<> NULL`. These always return NULL, not true/false. Use `IS NULL` and `IS NOT NULL`.
</Warning>

### Pattern matching with LIKE

Match string patterns using LIKE wildcards:

```sql theme={null}
SELECT user_id, email, name, code
FROM company_data."123"
WHERE
  email LIKE '%@gmail.com'      -- Ends with @gmail.com
  AND name LIKE 'John%'          -- Starts with John
  AND code LIKE 'A__'            -- A followed by exactly 2 characters
```

| Wildcard | Matches                 |
| -------- | ----------------------- |
| `%`      | Zero or more characters |
| `_`      | Exactly one character   |

For case-insensitive matching, normalize the case first:

```sql theme={null}
WHERE LOWER(email) LIKE '%@gmail.com'
```

***

## Date and time filtering

### Current date comparisons

Filter relative to the current date:

```sql theme={null}
SELECT user_id, event_date, created_at
FROM company_data."123"
WHERE
  -- Last 30 days
  event_date >= CURRENT_DATE - INTERVAL '30' DAY

  -- Last 3 months
  AND created_at >= CURRENT_DATE - INTERVAL '3' MONTH
```

### Date ranges

Filter within specific date ranges:

```sql theme={null}
SELECT user_id, email, event_date
FROM company_data."123"
WHERE
  event_date BETWEEN '2024-01-01' AND '2024-12-31'
```

### Extracting date parts

Filter by specific date components:

```sql theme={null}
SELECT user_id, event_date, event_type
FROM company_data."123"
WHERE
  EXTRACT(YEAR FROM event_date) = 2024
  AND EXTRACT(MONTH FROM event_date) IN (1, 2, 3)
  AND EXTRACT(DAYOFWEEK FROM event_date) = 1  -- Sunday
```

***

## Price filtering

Control costs by filtering on the special `_price_cpm_usd` column:

```sql theme={null}
SELECT user_id, email, category
FROM company_data."123"
WHERE
  category = 'premium'
  AND _price_cpm_usd <= 1.00  -- Max $1 per 1000 rows
```

<Tip>
  Always include price filtering to control data acquisition costs, especially when creating materialized views.
</Tip>

***

## Transforming data

### Expressions in SELECT

Compute values using expressions:

```sql theme={null}
SELECT
  quantity * unit_price AS line_total,
  (quantity * unit_price) * 1.0825 AS total_with_tax,
  first_name || ' ' || last_name AS full_name
FROM company_data."123"
```

### CASE expressions

Apply conditional logic:

```sql theme={null}
SELECT
  user_id,
  CASE
    WHEN lifetime_value > 1000 THEN 'platinum'
    WHEN lifetime_value > 500 THEN 'gold'
    WHEN lifetime_value > 100 THEN 'silver'
    ELSE 'bronze'
  END AS tier
FROM company_data."123"
```

### String transformations

Clean and format text:

```sql theme={null}
SELECT
  UPPER(name) AS uppercase_name,
  LOWER(email) AS lowercase_email,
  TRIM(user_input) AS cleaned_input,
  CONCAT(city, ', ', state) AS location,
  SUBSTRING(phone, 1, 3) AS area_code
FROM company_data."123"
```

### Type casting

Convert between types:

```sql theme={null}
SELECT
  CAST(string_number AS LONG) AS numeric_value,
  CAST(timestamp_value AS DATE) AS date_only,
  CAST(amount AS DECIMAL) AS precise_amount
FROM company_data."123"
```

***

## Working with complex types

### Accessing array elements

Use bracket notation with zero-based indexing:

```sql theme={null}
SELECT
  identifiers[0] AS first_id,
  identifiers[1] AS second_id,
  tags[0] AS primary_tag
FROM company_data."123"
```

### Accessing struct fields

Use dot notation:

```sql theme={null}
SELECT
  person.name,
  person.age,
  address.city,
  address.postal_code
FROM company_data."123"
```

### Nested access

Combine notation for deeply nested data:

```sql theme={null}
SELECT
  -- Array of structs
  identifiers[0].type AS first_id_type,
  identifiers[0].value AS first_id_value,

  -- Struct with nested struct
  user.preferences.language,
  user.preferences.timezone
FROM company_data."123"
```

### Accessing map values

Use `ELEMENT_AT` for maps:

```sql theme={null}
SELECT
  ELEMENT_AT(properties, 'color') AS color,
  ELEMENT_AT(metadata, 'source') AS source
FROM company_data."123"
```

### Expanding arrays with UNNEST

Convert array elements to rows:

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

This creates one row per array element. If a user has tags `['a', 'b', 'c']`, the result includes three rows for that user.

***

## Aggregating data

### Basic aggregations

Compute summary statistics:

```sql theme={null}
SELECT
  COUNT(1) AS total_rows,
  APPROX_COUNT_DISTINCT(user_id) AS unique_users,
  SUM(amount) AS total_amount,
  AVG(score) AS average_score,
  MIN(created_at) AS earliest,
  MAX(created_at) AS latest
FROM company_data."123"
```

### Grouping

Aggregate by categories:

```sql theme={null}
SELECT
  category,
  COUNT(1) AS count,
  SUM(amount) AS total,
  AVG(amount) AS average
FROM company_data."123"
GROUP BY category
```

### Filtering aggregated results

Use `HAVING` to filter after aggregation:

```sql theme={null}
SELECT
  category,
  COUNT(1) AS count
FROM company_data."123"
GROUP BY category
HAVING COUNT(1) > 100
```

***

## Deduplication with QUALIFY

Remove duplicates based on a window function:

```sql theme={null}
SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1
```

This keeps only the most recent record for each user.

***

## Putting it together

Here's a complete example combining multiple techniques:

```sql theme={null}
SELECT
  user_id,
  LOWER(email) AS normalized_email,
  CASE
    WHEN age < 25 THEN '18-24'
    WHEN age < 35 THEN '25-34'
    WHEN age < 45 THEN '35-44'
    ELSE '45+'
  END AS age_bracket,
  identifiers[0].type AS primary_id_type,
  created_at
FROM company_data."123"
WHERE
  email IS NOT NULL
  AND status = 'active'
  AND created_at >= CURRENT_DATE - INTERVAL '90' DAY
  AND _price_cpm_usd <= 0.50
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY created_at DESC
) = 1
ORDER BY created_at DESC
LIMIT 1000 ROWS
```

***

## Related content

<CardGroup cols={2}>
  <Card title="Joining Datasets" icon="link" href="/guides/nql/joining-datasets">
    Combine data from multiple sources
  </Card>

  <Card title="NQL Syntax Reference" icon="code" href="/nql/general/syntax">
    Complete syntax documentation
  </Card>

  <Card title="Operators Reference" icon="calculator" href="/nql/operators">
    All comparison and logical operators
  </Card>

  <Card title="Functions Reference" icon="function" href="/nql/functions/index">
    String, date, and transformation functions
  </Card>
</CardGroup>