Execute queries via SQL. Requires private API key in Bearer token.
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{
"limit": 123,
"offset": 123,
"total": 123,
"results": [
{
"results": [
{
"count": 3485241
}
]
}
]
}Query API
Execute Queries
Overview of the Trench API
POST
/
queries
Execute queries via SQL. Requires private API key in Bearer token.
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{
"limit": 123,
"offset": 123,
"total": 123,
"results": [
{
"results": [
{
"count": 3485241
}
]
}
]
}This endpoint requires your private API key.
Examples
Quering event properties, context, and traits
To query a specific nested property, you can use theJSONExtract 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
SELECT * FROM events WHERE JSONExtract(context, 'country', 'String') = 'Denmark'
Joining identified users with their events
Allidentify 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'
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
Bearer authentication header of the form Bearer <token>, where <token> is your auth token.
Body
application/json
The queries to execute.
Example:
[
"SELECT COUNT(*) FROM events WHERE event = \"UserSignedUp\""
]
Response
200 - application/json
The queries have been successfully executed.
The limit of the pagination.
The offset of the pagination.
The total number of results. If null, the total is unknown.
The results of the queries, returned in the same order as the queries.
Example:
[{ "results": [{ "count": 3485241 }] }]
Was this page helpful?