> AI agents: this is one page from PostHog's docs. Full index of Markdown docs for LLMs: https://posthog.com/llms.txt # SQL expressions Canonical URL: https://posthog.com/docs/sql/expressions > **Cloud region:** This page uses US Cloud hosts. For an EU Cloud project, use `https://eu.posthog.com` instead of `https://us.posthog.com` (app host for links). ## On this page - [Accessible data](#accessible-data) - [Operators](#operators) - [Functions and aggregations](#functions-and-aggregations) - [Use cases](#use-cases) SQL expressions enable you to directly access, modify, and aggregate data in many places in PostHog including: - Filters - Trends series - Breakdowns - [Retention](https://posthog.com/docs/product-analytics/retention.md) breakdowns - [Funnel](https://posthog.com/docs/product-analytics/funnels.md) aggregations - [User paths](https://posthog.com/docs/product-analytics/paths.md) - [Session replays](https://posthog.com/docs/session-replay.md) - [Dashboards](https://posthog.com/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) > **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](https://posthog.com/docs/product-analytics/person-properties.md) (`person.properties`) - `event` - `elements_chain` (from [autocapture](https://posthog.com/tutorials/hogql-autocapture.md)) - `timestamp` - `distinct_id` - `person_id` - When [joined](https://posthog.com/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](https://posthog.com/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](https://posthog.com/docs/sql/clickhouse-functions.md) like `dateDiff()` and `concat()` and [aggregations](https://posthog.com/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` | > **Date literals parse in your project's timezone, not UTC.** `toDateTime('2026-07-17 06:42:00')` means 06:42 in project time. A UTC timestamp pasted into a literal can shift by hours, and a recency filter built this way returns no rows and looks like missing data. For relative windows, prefer `now() - interval 20 minute`. For an absolute instant, pass the timezone explicitly: `toDateTime('2026-07-17 06:42:00', 'UTC')`. ## Use cases - Checking if a property or [autocapture element chain](https://posthog.com/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.cart.total`, or person properties such as `person.properties.$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](https://posthog.com/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](https://posthog.com/tutorials/hogql-autocapture.md) like `extract(elements_chain, '[:|"]attr__id="(.*?)"')`.