Skip to main content
POST
/
queries
Execute queries via SQL. Requires private API key in Bearer token.
curl --request POST \
  --url https://api.trench.dev/queries \
  --header 'Content-Type: application/json' \
  --data '{
  "queries": [
    "SELECT COUNT(*) FROM events WHERE event = \"UserSignedUp\""
  ]
}'
{
  "limit": 123,
  "offset": 123,
  "total": 123,
  "results": [
    {
      "results": [
        {
          "count": 3485241
        }
      ]
    }
  ]
}
This endpoint requires your private API key.
This endpoint allows you to execute read-only queries against your Trench data. The endpoint will proxy the request to the underlying ClickHouse database. For more information about ClickHouse’s query language, see the official documentation.

​
Examples

​
Quering event properties, context, and traits

To query a specific nested property, you can use the JSONExtract function. For example, to query all events where the totalAccounts property is greater than 3, you can use the following query: 
SELECT * FROM events WHERE JSONExtract(properties, 'totalAccounts', 'UInt64') > 3
Similarly, you can query the context and traits: 
SELECT * FROM events WHERE JSONExtract(context, 'country', 'String') = 'Denmark'

​
Joining identified users with their events

All identify calls are sent to the same underlying events ClickHouse table, so you can join events with identified users using the userId column. For example, to query all events for a user with the ID user-123, you can use the following query: 
SELECT * FROM events WHERE userId = 'user-123'
To get the tracking events and the user’s most recently provided email, you can join the track and identify event types: 
SELECT 
    i.email,
    e.*
FROM 
    events e 
LEFT JOIN 
    (SELECT userId, type, JSONExtract(traits, 'email', 'String') AS email FROM events) i 
ON 
    e.userId = i.userId
WHERE 
    e.type = 'track' 
    AND i.type = 'identify';

Body

application/json
​
queries
string[]
required

The queries to execute.

Example:
[
  "SELECT COUNT(*) FROM events WHERE event = \"UserSignedUp\""
]

Response

200 - application/json

The queries have been successfully executed.

​
limit
number | null
required

The limit of the pagination.

​
offset
number | null
required

The offset of the pagination.

​
total
number | null
required

The total number of results. If null, the total is unknown.

​
results
object[]
required

The results of the queries, returned in the same order as the queries.

Example:
[{ "results": [{ "count": 3485241 }] }]