Query data

RawTree uses SQL for analytics over ingested unstructured data.

Start with a small query

rtree query "SELECT * FROM events LIMIT 10"

Use LIMIT while exploring a table.

Aggregate events

rtree query "
  SELECT action, count() AS total
  FROM events
  GROUP BY action
  ORDER BY total DESC
  LIMIT 10
"

Query nested fields

Use dot notation for nested fields.

rtree query "
  SELECT user.id, count() AS total
  FROM events
  GROUP BY user.id
  ORDER BY total DESC
  LIMIT 10
"

Read original rows

RawTree stores the original JSON object for each inserted row in the virtual __raw_data column. Query it when you need to debug ingestion, check inferred fields, or recover the source payload.

SELECT __raw_data FROM events LIMIT 10;

You can also use dot notation on __raw_data to inspect fields inside the original object.

SELECT __raw_data.user.id, __raw_data.event FROM events LIMIT 10;

Return machine-readable output

rtree query --json "SELECT action, count() AS total FROM events GROUP BY action LIMIT 10"

The API response includes column metadata, rows, statistics, and optional hints.

API query

curl -X POST "https://api.rawtree.com/v1/query" \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM events LIMIT 10"}'

Build an endpoint from a query

RawTree query requests accept {"sql":"..."}. If your application exposes an analytical endpoint, build the final SQL in your application code, then send it in the sql field.

This example returns daily action counts for a time window. There is no caller-provided LIMIT; the endpoint is bounded by the time window and grouped output.

const RAWTREE_API_URL = "https://api.rawtree.com";

export async function GET(request: Request) {
  const url = new URL(request.url);
  const start = url.searchParams.get("start");
  const end = url.searchParams.get("end");

  if (!start || !end) {
    return Response.json(
      { error: "start and end query parameters are required" },
      { status: 400 }
    );
  }

  const sql = `
    SELECT
      toDate(event_time) AS day,
      action,
      count() AS events,
      uniq(user) AS users
    FROM events
    WHERE event_time >= toDateTime('${start}')
      AND event_time < toDateTime('${end}')
    GROUP BY day, action
    ORDER BY day ASC, events DESC
  `;

  const response = await fetch(`${RAWTREE_API_URL}/v1/query`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${process.env.RAWTREE_TOKEN}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ sql }),
  });

  return Response.json(await response.json(), { status: response.status });
}

This assumes start and end arrive in the expected datetime format. For user-controlled dimensions, prefer allowlists before putting values into SQL.

Query rules

• Send query requests as {"sql":"SELECT ..."}.
• Only read-only SQL is accepted through the query endpoint.
• Use dot notation for nested fields, for example user.id.
• Use __raw_data when you need the original inserted JSON object.
• Treat parameterization as application code: build the final SQL string before sending it.
• Use casts when data needs a specific type, for example value::Float64, toString(user), or CAST(id AS UInt64).
• Add LIMIT while exploring raw rows. Aggregated endpoints can be bounded by time windows and grouping instead.
• Use logs when a query fails; errors include messages and may include hints.

See SQL reference for syntax, functions, aggregate functions, and data types.