Accessing data using SQL

Strings and quotes

Quotation symbols work the same way they would work with ClickHouse, which inherits from ANSI SQL:

• Single quotes (') for Strings literals.
• Double quotes (") and Backticks (`) for DataBase identifiers.

For example:

SELECT * FROM events WHERE properties.`$browser` = 'Chrome'

Types

Types (and names) for the accessible data can be found in the database, properties tabs in data management as well as in the SQL tab for external sources. They include:

• STRING (default)
• JSON (accessible with dot or bracket notation)
• DATETIME(in ISO 8601, read more in our data docs)
• INTEGER
• FLOAT
• BOOLEAN

For example:

SELECT round(properties.$screen_width * properties.$screen_height / 1000000, 2) as `Screen MegaPixels` FROM events LIMIT 100

This works because $screen_width and $screen_height are both defined as numeric properties. Thus you can multiply them.

To cast a string property into a different type, use type conversion functions, such astoString, toDate, toFloat, JSONExtractString, JSONExtractInt, and more.

Property access

To access a property stored on an event or person, just use dot notation. For example properties.$browser or person.properties.$initial_browser. You can also use bracket notation like properties['$feature/cool-flag'].

Nested property or JSON access, such as properties.$some.nested.property, works as well.

PostHog's properties include 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.

Some queries can error when accessing null values. To avoid this, use the COALESCE function to replace null values with a default value or filter NULL values with IS NOT NULL and use assumeNotNull to cast a column to a non-null type.

Actions

To use actions in SQL insights, use the matchesAction() function. For example, to get a count of the action clicked homepage button, you can do:

SELECT count() 
FROM events 
WHERE matchesAction('clicked homepage button')

For more customization when using actions, start by selecting you action in the actions tab under data management.

In the action details under "Matching events," click the export dropdown and select "Edit SQL directly."

This opens an SQL insight using the action. You can then copy parts of the SQL, like the WHERE filter or columns under SELECT, to use in your own insights.

Cohorts

To use cohorts in SQL insights, simply filter where person_id IN COHORT '{name}'.

For example, to get a count of users in the Power user cohort:

select count()
from persons
where id IN COHORT 'Power users'

To get a count of events for users in the Power user cohort:

select count()
from events
where person_id IN COHORT 'Power user'