# SQL expressions - Docs

SQL expressions enable you to directly access, modify, and aggregate data in many places in PostHog including:

-   Filters
-   Trends series
-   Breakdowns
-   [Funnel](/docs/product-analytics/funnels.md) aggregations
-   [User paths](/docs/product-analytics/paths.md)
-   [Session replays](/docs/session-replay.md)
-   [Dashboards](/docs/product-analytics/dashboards.md)
-   The [activity tab](https://us.posthog.com/events)

![SQL trends breakdown filter](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_11_at_10_35_25_2x_35d8460052.png)![SQL trends breakdown filter](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_11_at_10_34_54_2x_0bf110d266.png)

> **Tip:** If you're having trouble getting results from your expression, try debugging by using a different visualization (trends table often works best as it shows all values returned) or breaking down your expression into pieces and testing each one.

## Accessible data

SQL expressions can access data like:

-   event properties (`properties`)
-   [person properties](/docs/product-analytics/person-properties.md) (`person.properties`)
-   `event`
-   `elements_chain` (from [autocapture](/tutorials/hogql-autocapture.md))
-   `timestamp`
-   `distinct_id`
-   `person_id`
-   When [joined](/docs/data-warehouse/join.md), data warehouse source properties

Properties can be accessed with dot notation like `person.properties.$initial_browser` which also works for nested or JSON properties. They can also be accessed with bracket notation like `properties['$feature/cool-flag']`.

> **Note:** PostHog's properties always include `$` as a prefix, while custom properties do not (unless you add it).

Property identifiers must be known at query time. For dynamic access, use the JSON manipulation functions from below on the `properties` field directly.

### Types

Types (and names) for the accessible data can be found in the [database](https://us.posthog.com/data-management/database) and [properties](https://us.posthog.com/data-management/properties) tabs in data management. They include:

-   `STRING` (default)
-   `JSON` (accessible with dot or bracket notation)
-   `DATETIME`(in `ISO 8601`, [read more in our data docs](/docs/data/timestamps.md))
-   `INTEGER`
-   `NUMERIC`(AKA float)
-   `BOOLEAN`

Types can be converted using functions like `toString`, `toDate`, `toFloat`, `JSONExtractString`, `JSONExtractInt`, and more.

## Operators

Expressions can use operators to filter and combine data. These include:

-   Comparison operators like `=`, `!=`, `<`, or `>=`
-   Logical operators like `AND`, `OR`, `IS` or `NOT`
-   Arithmetic operators like `+`, `-`, `*`, `/`

## Functions and aggregations

You can filter, modify, or aggregate accessed data with [supported ClickHouse functions](/docs/sql/clickhouse-functions.md) like `dateDiff()` and `concat()` and [aggregations](/docs/hogql/aggregations.md) like `sumIf()` and `count()`.

Here are some of the most common and useful ones:

### Comparisons

| Function | Definition |
| --- | --- |
| if(cond, then, else) | Checks a condition, and if true (or non-zero), returns the result of an expression |
| multiIf(cond1, then1, cond2, then2, ..., else) | Enables chaining multiple if statements together, each with a condition and return expression |
| in(value, set) | Checks if an array or string contains a value |
| match(value, regexp) | Checks whether a string matches a regular expression pattern |
| like | Checks if a string matches a pattern that contains string(s) and symbols %, _, \\ (escaped literals) |

### Aggregations

| Aggregation | Definition |
| --- | --- |
| count | Counts the values. If you want a condition, use sumIf |
| count(distinct) | Counts the number of uniqExact values |
| uniq | Calculates the approximate number of different values (uniqExact is slower but exact). |
| uniqExact | Calculates the exact number of different argument values (uniq is faster and you should use it if a close approximation is good enough). |
| sum | Calculates the total (sum) numeric value |
| sumIf(column, cond) | Calculates the total (sum) numeric value for values (column) meeting a condition (cond) |
| avg | Calculates the average numeric value |
| median | Computes an approximate middle (50%) value for a numeric data sequence. |

### Strings

| Function | Definition |
| --- | --- |
| extract(haystack, pattern) | Extracts a fragment of a string (haystack) using a regular expression (pattern) like extract(properties.$current_url, 'ref=([^&]*)') |
| concat(s1, s2, ...) | Concatenates strings (s1, s2, etc.) listed without separator |
| splitByChar(separator, s) | Splits string (s) into substrings separated by a specified character (separator) |
| replaceOne(haystack, pattern, replacement) | Replace the first occurrence of matching a substring (pattern) with a replacement string (replacement). Example: replaceOne(properties.$current_url, 'https://us.posthog.com', '/') |
| replaceRegexpOne(haystack, pattern, replacement) | Replace the first occurrence of matching a regular expression (pattern) with a replacement string (replacement) |
| substring(s, start) | Extracts a substring from a string (s) starting at index (start) |

### Dates

| Function | Definition |
| --- | --- |
| dateDiff('unit', startdate, enddate) | Returns the count in unit between startdate and enddate |
| toDayOfWeek, toHour, toMinute | Converts date number of day of week (1-7), hour in 24-hour time (0-23), and minute in hour (0-59) like toHour(timestamp) |
| now(), today(), yesterday() | Returns the current time, date, or yesterday's date respectively |
| interval | A length of time for use in arithmetic operations with other dates and times like person.properties.trial_started + interval 30 day |

## Use cases

-   Checking if a property or [autocapture element chain](/tutorials/hogql-autocapture.md) contains a specific value or any of an array of values using `in` or `match`.

-   Modifying the display string in the visualization by extracting or concatenating properties using `concat()`, `+`, `extract()`, or `replaceOne` like `concat('OS Version: ', properties.$os_version)`.

-   Grouping or binning events based on properties using `if()`, `multiIf()` like `multiIf(properties.$device_type == 'Desktop', 'Desktop', properties.$os == 'iOS', 'iOS', 'Non-iOS')`.

-   Accessing nested properties such as `properties.$set.$geoip_city_name`.

-   Filtering for events that happened in the last X minutes, hours, or days with `dateDiff()`, `now()`, and `interval` like `dateDiff('minute', timestamp, now()) < 30`.

-   Creating percentages by calculating the sum of one property over the sum of all related properties inline with `sum()`, `/`, `+`, and `*` like `sumIf(1, properties.$browser = 'Chrome') / sumIf(1, properties.$browser = 'Safari' or properties.$browser = 'Chrome')`

-   Binning events based on time of day, week, and month with `toHour`, `toDayOfWeek`, `toStartOfWeek`, `toMonth` like `multiIf(5 >= toHour(timestamp) and toHour(timestamp) < 12, 'morning', 12 >= toHour(timestamp) and toHour(timestamp) < 17, 'afternoon', 'night')`

-   Breaking down by multiple properties using `concat()` like `concat(properties.$os_name, ' - ', properties.$os_version)`.

-   Matching URL patterns with `like` like `(properties.$current_url LIKE '%/blog%')`

-   Filter null property values with `IS NOT NULL` like `person.properties.$initial_utm_source IS NOT NULL`.

-   Breakdown by values in an [array](/tutorials/array-filter-breakdown.md) by using a combination of `JSONExtractArrayRaw` and `arrayJoin` like `arrayJoin(JSONExtractArrayRaw(properties.$active_feature_flags ?? '[]'), ',')`.

-   Extracting the ID from [autocaptured elements](/tutorials/hogql-autocapture.md) like `extract(elements_chain, '[:|"]attr__id="(.*?)"')`.

### Community questions

Ask a question

### Was this page useful?

HelpfulCould be better