HogQL

HogQL is our take on SQL (Structured Query Language), a language used to manage and access data. HogQL is effectively a wrapper around ClickHouse SQL, with a few tweaks, such as simplified event and person property access.

Is HogQL real SQL? Yes, it's a translation layer over ClickHouse SQL. You can use most ClickHouse features in HogQL, including JOINs and subqueries. To learn more about ClickHouse, and how it differs from traditional SQL databases, read PostHog's ClickHouse Manual.

HogQL is currently in public beta. This means it's not yet a perfect experience, and the language itself may still change. Follow along with the development here.

HogQL expressions

HogQL expressions can be used inside insights and dashboards as filters and/or breakdown values. When filtering or breaking down, select the "HogQL" tab, and add your expression.

HogQL trends breakdown filter

SQL insights

SQL insights enables you to create and edit insights using full SQL queries instead of the UI. Clicking the {} button in the top right corner of an insight shows you the SQL schema used to generate the insight which you can edit.

HogQL SQL insight

Event explorer

The event explorer replaces the "Live Events" tab in PostHog. It provides more functionality including date filtering, event counts, HogQL queries, aggregations and column configuration.

Event Explorer

Database

To display all the tables you can query, check out the new "Database" tab under "Data Management".

Database

We're working hard on making all these public. Follow along in the relevant Github issue, or send us a support ticket if you want to get access.

API access

Will there be API pricing? The HogQL API is free to use while it's in the public beta, and we work out the details. After we launch for real, we plan to charge a competitive rate for heavy usage. Stay tuned.

To access HogQL via the PostHog API, make a POST request to /api/project/:id/query with the following JSON payload:

JSON

{"query": {"kind": "HogQLQuery", "query": "select * from events limit 100"}}

The response is in the format:

TypeScript

export interface HogQLQueryResponse {
    /** The input query */
    query?: string
    /** An array of result arrays */
    results?: any[][]
    /** Returned column types */
    types?: string[]
    /** Returned column names/aliases */
    columns?: string[]
    /** Generated HogQL query with expressions inlined */
    hogql?: string
    /** Generated ClickHouse query for debugging */
    clickhouse?: string
}

While in the public beta, the response format may still change.

HogQL guide

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:

SQL

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

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.

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.

Property types

If you have specified a type for an event or person property under "Data Management", it will be returned in this type. All other properties are returned as strings.

For example:

SQL

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 as toFloat().

You can find a full list of properties and their types in your data management tab.

Supported ClickHouse functions

The following ClickHouse functions are enabled. This list is ever expanding. Please submit a pull request if we're missing something obvious.

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
max2
min2
multiplyDecimal
divideDecimal

Arrays and strings in common

empty
notEmpty
length
reverse
in
notIn

Arrays

array
range
arrayConcat
arrayElement
has
hasAll
hasAny
hasSubstr
indexOf
arrayCount
countEqual
arrayEnumerate
arrayEnumerateUniq
arrayPopBack
arrayPopFront
arrayPushBack
arrayPushFront
arrayResize
arraySlice
arraySort
arrayReverseSort
arrayUniq
arrayJoin
arrayDifference
arrayDistinct
arrayEnumerateDense
arrayIntersect
arrayReverse
arrayFilter
arrayFlatten
arrayCompact
arrayZip
arrayAUC
arrayMap
arrayFill
arraySplit
arrayReverseFill
arrayReverseSplit
arrayExists
arrayAll
arrayFirst
arrayLast
arrayFirstIndex
arrayLastIndex
arrayMin
arrayMax
arraySum
arrayAvg
arrayCumSum
arrayCumSumNonNegative
arrayProduct

Comparison

equals
notEquals
less
greater
lessOrEquals
greaterOrEquals

Logical

and
or
xor
not

Type conversions

toInt
toFloat
toDecimal
toDate
toDateTime
toUUID
toString
toJSONString
parseDateTime
parseDateTimeBestEffort

Dates and times

toTimeZone
timeZoneOf
timeZoneOffset
toYear
toQuarter
toMonth
toDayOfYear
toDayOfMonth
toDayOfWeek
toHour
toMinute
toSecond
toUnixTimestamp
toStartOfYear
toStartOfISOYear
toStartOfQuarter
toStartOfMonth
toLastDayOfMonth
toMonday
toStartOfWeek
toStartOfDay
toStartOfHour
toStartOfMinute
toStartOfSecond
toStartOfFiveMinutes
toStartOfTenMinutes
toStartOfFifteenMinutes
toTime
toISOYear
toISOWeek
toWeek
toYearWeek
age
dateDiff
dateTrunc
dateAdd
dateSub
timeStampAdd
timeStampSub
now
NOW
nowInBlock
today
yesterday
timeSlot
toYYYYMM
toYYYYMMDD
toYYYYMMDDhhmmss
addYears
addMonths
addWeeks
addDays
addHours
addMinutes
addSeconds
addQuarters
subtractYears
subtractMonths
subtractWeeks
subtractDays
subtractHours
subtractMinutes
subtractSeconds
subtractQuarters
timeSlots
formatDateTime
dateName
monthName
fromUnixTimestamp
toModifiedJulianDay
fromModifiedJulianDay
toIntervalSecond
toIntervalMinute
toIntervalHour
toIntervalDay
toIntervalWeek
toIntervalMonth
toIntervalQuarter
toIntervalYear

Strings

lengthUTF8
leftPad
rightPad
leftPadUTF8
rightPadUTF8
lower
upper
lowerUTF8
upperUTF8
isValidUTF8
toValidUTF8
repeat
format
reverseUTF8
concat
substring
substringUTF8
appendTrailingCharIfAbsent
convertCharset
base58Encode
base58Decode
tryBase58Decode
base64Encode
base64Decode
tryBase64Decode
endsWith
startsWith
trim
trimLeft
trimRight
encodeXMLComponent
decodeXMLComponent
extractTextFromHTML
ascii
concatWithSeparator

Searching in strings

position
positionCaseInsensitive
positionUTF8
positionCaseInsensitiveUTF8
multiSearchAllPositions
multiSearchAllPositionsUTF8
multiSearchFirstPosition
multiSearchFirstIndex
multiSearchAny
match
multiMatchAny
multiMatchAnyIndex
multiMatchAllIndices
multiFuzzyMatchAny
multiFuzzyMatchAnyIndex
multiFuzzyMatchAllIndices
extract
extractAll
extractAllGroupsHorizontal
extractAllGroupsVertical
like
ilike
notLike
notILike
ngramDistance
ngramSearch
countSubstrings
countSubstringsCaseInsensitive
countSubstringsCaseInsensitiveUTF8
countMatches
regexpExtract

Replacing in strings

replace
replaceAll
replaceOne
replaceRegexpAll
replaceRegexpOne
regexpQuoteMeta
translate
translateUTF8

Conditional

if
multiIf

Mathematical

e
pi
exp
log
ln
exp2
log2
exp10
log10
sqrt
cbrt
erf
erfc
lgamma
tgamma
sin
cos
tan
asin
acos
atan
pow
power
intExp2
intExp10
cosh
acosh
sinh
asinh
atanh
atan2
hypot
log1p
sign
degrees
radians
factorial
width_bucket

Rounding

floor
ceil
trunc
round
roundBankers
roundToExp2
roundDuration
roundAge
roundDown

Maps

map
mapFromArrays
mapAdd
mapSubtract
mapPopulateSeries
mapContains
mapKeys
mapValues
mapContainsKeyLike
mapExtractKeyLike
mapApply
mapFilter
mapUpdate

Splitting strings

splitByChar
splitByString
splitByRegexp
splitByWhitespace
splitByNonAlpha
arrayStringConcat
alphaTokens
extractAllGroups
ngrams
tokens

Bit

bitAnd
bitOr
bitXor
bitNot
bitShiftLeft
bitShiftRight
bitRotateLeft
bitRotateRight
bitSlice
bitTest
bitTestAll
bitTestAny
bitCount
bitHammingDistance

Bitmap

bitmapBuild
bitmapToArray
bitmapSubsetInRange
bitmapSubsetLimit
subBitmap
bitmapContains
bitmapHasAny
bitmapHasAll
bitmapCardinality
bitmapMin
bitmapMax
bitmapTransform
bitmapAnd
bitmapOr
bitmapXor
bitmapAndnot
bitmapAndCardinality
bitmapOrCardinality
bitmapXorCardinality
bitmapAndnotCardinality

URLs

protocol
domain
domainWithoutWWW
topLevelDomain
firstSignificantSubdomain
cutToFirstSignificantSubdomain
cutToFirstSignificantSubdomainWithWWW
port
path
pathFull
queryString
fragment
queryStringAndFragment
extractURLParameter
extractURLParameters
extractURLParameterNames
URLHierarchy
URLPathHierarchy
encodeURLComponent
decodeURLComponent
encodeURLFormComponent
decodeURLFormComponent
netloc
cutWWW
cutQueryString
cutFragment
cutQueryStringAndFragment
cutURLParameter

JSON

isValidJSON
JSONHas
JSONLength
JSONArrayLength
JSONType
JSONExtractUInt
JSONExtractInt
JSONExtractFloat
JSONExtractBool
JSONExtractString
JSONExtractKey
JSONExtractKeys
JSONExtractRaw
JSONExtractArrayRaw
JSONExtractKeysAndValuesRaw

Geo

greatCircleDistance
geoDistance
greatCircleAngle
pointInEllipses
pointInPolygon

Nullable

isNull
isNotNull
coalesce
ifNull
nullIf
assumeNotNull
toNullable

Tuples

tuple
tupleElement
untuple
tupleHammingDistance
tupleToNameValuePairs
tuplePlus
tupleMinus
tupleMultiply
tupleDivide
tupleNegate
tupleMultiplyByNumber
tupleDivideByNumber
dotProduct

Time window

tumble
hop
tumbleStart
tumbleEnd
hopStart
hopEnd

Distance window

L1Norm
L2Norm
LinfNorm
LpNorm
L1Distance
L2Distance
LinfDistance
LpDistance
L1Normalize
L2Normalize
LinfNormalize
LpNormalize
cosineDistance

Other

isFinite
isInfinite
ifNotFinite
isNaN
bar
transform
formatReadableDecimalSize
formatReadableSize
formatReadableQuantity
formatReadableTimeDelta

Supported aggregations

The following aggregations are enabled. This list is ever expanding. Please submit a pull request if we're missing something obvious.

Standard aggregate functions

count
countIf
min
minIf
max
maxIf
sum
sumIf
avg
avgIf
any
anyIf
stddevPop
stddevPopIf
stddevSamp
stddevSampIf
varPop
varPopIf
varSamp
varSampIf
covarPop
covarPopIf
covarSamp
covarSampIf

ClickHouse-specific aggregate functions

anyHeavy
anyHeavyIf
anyLast
anyLastIf
argMin
argMinIf
argMax
argMaxIf
avgWeighted
avgWeightedIf
groupArray
groupUniqArray
groupArrayInsertAt
groupArrayInsertAtIf
groupArrayMovingAvg
groupArrayMovingAvgIf
groupArrayMovingSum
groupArrayMovingSumIf
groupBitAnd
groupBitAndIf
groupBitOr
groupBitOrIf
groupBitXor
groupBitXorIf
groupBitmap
groupBitmapIf
groupBitmapAnd
groupBitmapAndIf
groupBitmapOr
groupBitmapOrIf
groupBitmapXor
groupBitmapXorIf
sumWithOverflow
sumWithOverflowIf
deltaSum
deltaSumIf
deltaSumTimestamp
deltaSumTimestampIf
sumMap
sumMapIf
minMap
minMapIf
maxMap
maxMapIf
skewSamp
skewSampIf
skewPop
skewPopIf
kurtSamp
kurtSampIf
kurtPop
kurtPopIf
uniq
uniqIf
uniqExact
uniqExactIf
uniqHLL12
uniqHLL12If
uniqTheta
uniqThetaIf
simpleLinearRegression
simpleLinearRegressionIf
contingency
contingencyIf
cramersV
cramersVIf
cramersVBiasCorrected
cramersVBiasCorrectedIf
theilsU
theilsUIf
maxIntersections
maxIntersectionsIf
maxIntersectionsPosition
maxIntersectionsPositionIf

HogQL expressions

SQL insights

Event explorer

Database

API access

HogQL guide

Strings and quotes

Property access

Property types

Supported ClickHouse functions

Type conversion

Arithmetic

Arrays and strings in common

Arrays

Comparison

Logical

Type conversions

Dates and times

Strings

Searching in strings

Replacing in strings

Conditional

Mathematical

Rounding

Maps

Splitting strings

Bit

Bitmap

URLs

JSON

Geo

Nullable

Tuples

Time window

Distance window

Other

Supported aggregations

Standard aggregate functions

ClickHouse-specific aggregate functions

Questions?

Was this page useful?

Insights