HogQL expressions

Last updated:

|Edit this page

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

HogQL 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

HogQL expressions can access data like:

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

FunctionDefinition
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
likeChecks if a string matches a pattern that contains string(s) and symbols %, _, \ (escaped literals)

Aggregations

AggregationDefinition
countCounts the values. If you want a condition, use sumIf
count(distinct)Counts the number of uniqExact values
uniqCalculates the approximate number of different values (uniqExact is slower but exact).
uniqExactCalculates the exact number of different argument values (uniq is faster and you should use it if a close approximation is good enough).
sumCalculates the total (sum) numeric value
sumIf(column, cond)Calculates the total (sum) numeric value for values (column) meeting a condition (cond)
avgCalculates the average numeric value
medianComputes an approximate middle (50%) value for a numeric data sequence.

Strings

FunctionDefinition
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

FunctionDefinition
dateDiff('unit', startdate, enddate)Returns the count in unit between startdate and enddate
toDayOfWeek, toHour, toMinuteConverts 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
intervalA 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="(.*?)"').

Questions?

  • Alexander
    7 months ago

    How can you get a Date from a timestamp string?

    e.g. '2024-06-25T23:00:12.316883Z'

  • Jake
    7 months ago

    Using HogQL/SQL to add users to cohort

    Is it at all possible to use custom SQL queries to find users that meet some criteria, and then add these users to a cohort? If so, how could I do this?

  • Caleb
    10 months ago

    I need to create a cohort

    I need to create a cohort of persons (users) that belong to groups (teams) that were created in the last 90 days. Team has a property called teamCreatedDate - and I am able to create an insight for this easily with hogQL. But how do I create a cohort of users that fall under this team? The only property that the users (persons) have that matches the property on the team (groups) is the team name which is named team (Which is the name of the team). Can hogQL help me do this? Is there a better way to do this?

    • Abigail(she/her)
      9 months ago

      Hi Mike! No it didn't as part of this discussion. I've just created one for you now here. Please add in whatever context you think is relevant for your use case :)

  • Emiliano
    a year ago

    The People tab has a "First Seen" column, how to "Last Seen"?

    I can't find how to add a "Last Seen" column in the People tab. It should be possible, right? Just get their latest event/identify and sort by that.

    • Ansh
      7 months ago

      I don't think this exists currently.

  • Nathan
    a year ago

    Filtering by session time

    Is it possible to query/filter persons in HogQL based on the amount of time they have spent in sessions (from session replay)? In the Session replay, I often filter to only include sessions of people who have spent over 30 active seconds on the site. I'd love to apply this same filter to other users/lists/cohorts for better analysis

  • Robert
    a year ago

    Number of unique referring URLs

    Hi I'm trying to aggregate the number of referring URLs with uniqExact(properties.$referring_url) and it's not giving me any results for some reason. If I just use count() I see data

  • harvey
    a year ago

    Group Analytics with HogQL

    Is there a way to access group properties with HogQL?

    For instance, if I want to calculate a score based on certain group events

    sumIf(
    10,
    toInt(properties.company.properties.user_count) > 2
    ) + sumIf(
    5,
    toInt(properties.company.properties.other_property_count) > 5
    ) + sumIf(
    -1,
    toInt(properties.company.properties.user_count) = 1
    )
    • Marcus
      a year ago

      Hey Harvey, that is not directly supported, since a user could have events from multiple groups. Could you share a bit more context on your use case?

  • fecilia
    a year ago

    How do I calculate Total count of Unique users?

    I would like to see the cumulative value of unique users using a feature. Currently only I can see the total count of feature being used, or monthly/weekly/daily unique users count.

    Can HogQL calculate Total count of Unique users?

  • ting
    a year ago

    Calculate Percentiles in the dashboard

    It seems that there is no way to calculate Percentiles on the dashboard. A similar method is the median. For example, the meaning of p75 is that If the 75th percentile (p75) of the Largest Contentful Paint metric is 2.29 seconds then that means that 75% of users wait less than 2.29 seconds for the main page content, and 25% wait longer than that.

  • Julia
    a year ago

    Timestamp to date

    Hello! Problem with timestamp. I trying to get date, but all the functions return me nothing Снимок экрана 2024-02-28 в 15.26.19.png

    • Justin
      a year ago

      One other thought is maybe the number of results getting returned is so large? May be useful to try with a subset of the data.

  • Noemi
    a year ago

    Naming expressions

    When using an expression on the breakdown, the whole expression becomes the column name on the table, which is confusing. Is it possible to rename it? I'd like to do something like:

    replaceRegexpOne(... arcane magic here...) AS "Normalised Current URL"
  • Sean
    a year ago

    Escaping literals in regex

    If I want to use regex function e.g. regexpExtract() in a HogQL expression how can I escape characters in my regex expressions?

    Using a backslash results in the error "no viable alternative at input 'regexpExtract('properties.state'"

    Thanks!

    • Julian
      a year ago

      Did you try double single quotes to escape like described at https://clickhouse.com/docs/en/sql-reference/syntax#string? We might not support all the others.

    • Sean
      Authora year ago

      Thanks Julian! If I understand correctly, these docs state that only single quotes can be escaped using a single quote, and that a backslash must be used to escape other character. I am trying to escape the regex quantifier '?' - sounds like this is not yet possible in HogQL?

Was this page useful?

Next article

Supported ClickHouse functions

This is an ever-expanding list of enabled ClickHouse functions. You can find their full definitions in the ClickHouse documentation . Additionally, we include a list of popular ones and their uses in the HogQL expressions and SQL insight documentation. Type conversion toInt toFloat toDecimal toDate toDateTime toUUID toString toJSONString parseDateTime parseDateTimeBestEffort Arithmetic plus minus multiply divide intDiv intDivOrZero modulo moduloOrZero positiveModulo negate abs gcd lcm max…

Read next article