AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

tools-and-features-hogql

HogQL queries for PostHog analytics

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--tools-and-features-hogql at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 9

references/expressions.md

SQL expressions - Docs

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

SQL trends breakdown filterSQL trends breakdown filter

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 (person.properties)
  • event
  • elements_chain (from autocapture)
  • timestamp
  • distinct_id
  • person_id
  • When joined, 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 and 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)
  • 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 like dateDiff() and concat() and aggregations 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 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 by using a combination of JSONExtractArrayRaw and arrayJoin like arrayJoin(JSONExtractArrayRaw(properties.$active_feature_flags ?? '[]'), ',').

  • Extracting the ID from autocaptured elements like extract(elements_chain, '[:|"]attr__id="(.*?)"').

Community questions

Ask a question

Was this page useful?

HelpfulCould be better