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

Response

200 - application/json

The queries have been successfully executed.

The response is of type object.