Execute Queries

Overview of the Trench API

POST /queries

Execute queries via SQL. Requires private API key in Bearer token.

cURL

curl --request POST \
  --url https://api.trench.dev/queries \
  --header 'Authorization: Bearer <token>' \
  --header 'Content-Type: application/json' \
  --data '
{
  "queries": [
    "SELECT COUNT(*) FROM events WHERE event = \"UserSignedUp\""
  ]
}
'

import requests

url = "https://api.trench.dev/queries"

payload = { "queries": ["SELECT COUNT(*) FROM events WHERE event = \"UserSignedUp\""] }
headers = {
    "Authorization": "Bearer <token>",
    "Content-Type": "application/json"
}

response = requests.post(url, json=payload, headers=headers)

print(response.text)

const options = {
  method: 'POST',
  headers: {Authorization: 'Bearer <token>', 'Content-Type': 'application/json'},
  body: JSON.stringify({queries: ['SELECT COUNT(*) FROM events WHERE event = "UserSignedUp"']})
};

fetch('https://api.trench.dev/queries', options)
  .then(res => res.json())
  .then(res => console.log(res))
  .catch(err => console.error(err));

<?php

$curl = curl_init();

curl_setopt_array($curl, [
  CURLOPT_URL => "https://api.trench.dev/queries",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => json_encode([
    'queries' => [
        'SELECT COUNT(*) FROM events WHERE event = "UserSignedUp"'
    ]
  ]),
  CURLOPT_HTTPHEADER => [
    "Authorization: Bearer <token>",
    "Content-Type: application/json"
  ],
]);

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

package main

import (
	"fmt"
	"strings"
	"net/http"
	"io"
)

func main() {

	url := "https://api.trench.dev/queries"

	payload := strings.NewReader("{\n  \"queries\": [\n    \"SELECT COUNT(*) FROM events WHERE event = \\\"UserSignedUp\\\"\"\n  ]\n}")

	req, _ := http.NewRequest("POST", url, payload)

	req.Header.Add("Authorization", "Bearer <token>")
	req.Header.Add("Content-Type", "application/json")

	res, _ := http.DefaultClient.Do(req)

	defer res.Body.Close()
	body, _ := io.ReadAll(res.Body)

	fmt.Println(string(body))

}

HttpResponse<String> response = Unirest.post("https://api.trench.dev/queries")
  .header("Authorization", "Bearer <token>")
  .header("Content-Type", "application/json")
  .body("{\n  \"queries\": [\n    \"SELECT COUNT(*) FROM events WHERE event = \\\"UserSignedUp\\\"\"\n  ]\n}")
  .asString();

require 'uri'
require 'net/http'

url = URI("https://api.trench.dev/queries")

http = Net::HTTP.new(url.host, url.port)
http.use_ssl = true

request = Net::HTTP::Post.new(url)
request["Authorization"] = 'Bearer <token>'
request["Content-Type"] = 'application/json'
request.body = "{\n  \"queries\": [\n    \"SELECT COUNT(*) FROM events WHERE event = \\\"UserSignedUp\\\"\"\n  ]\n}"

response = http.request(request)
puts response.read_body

200

{
  "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';

Authorizations

Authorization · string · header · required

Bearer authentication header of the form Bearer <token>, where <token> is your auth token.

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 }] }]