> ## Documentation Index
> Fetch the complete documentation index at: https://docs.superleap.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Execute Superleap Object Query

> Runs a read-only SQL analytics query against a Superleap object.

## Overview

Run a Superleap Object Query against a Superleap object and return the resulting records - for analytics and reporting use cases such as field selection, counts, grouped summaries, and filtered datasets.

<Note>
  Replace `<domain>` with your Superleap workspace domain. For example, `app.superleap.com`, `sandbox.superleap.dev`, or your tenant-specific domain.
</Note>

<Warning>
  Execute only read-only `SELECT` queries. Discover the object slug and field slugs first; do not guess table or field names.
</Warning>

## Authentication

Bearer token scoped to the Superleap organization.

### Headers

<ParamField header="Authorization" type="string" required>
  Bearer token for authentication. [Click here](https://app.superleap.com/settings/apiAccess) to generate one.
</ParamField>

<ParamField header="Content-Type" type="string" default="application/json" required>
  Must be `application/json`.
</ParamField>

## Request Body

<ParamField body="query" type="string" required>
  Raw PostgreSQL `SELECT` query to execute. The top-level `FROM` table must exactly match `slug`.
</ParamField>

<ParamField body="slug" type="string" required>
  Base CRM object slug used in the query's top-level `FROM` clause, for example `account`, `contact`, `deal`, or another object slug from your workspace.
</ParamField>

## Query Rules

The `query` field is written in the **Superleap Query Language**, a PostgreSQL-compatible `SELECT` syntax that understands your objects and their relations.

### Structure

Only `SELECT` queries are accepted. The top-level `FROM` must be the object slug exactly as returned by `list_objects` (`'lead'`, never `'leads'`), and it must match the request's `slug`. Always end the query with a `LIMIT`; use `LIMIT 100` when you have no specific number in mind.

### Dates and times

Every date and datetime field stores **epoch milliseconds** as a bigint. Compare epoch to epoch in `WHERE` clauses, and compute the boundaries inside the SQL instead of pasting precomputed numbers, date strings, `CURRENT_DATE`, or `to_timestamp(...)` comparisons:

```sql theme={null}
-- fixed boundary (midnight in the org timezone)
date_part('epoch', TIMESTAMP '2026-07-16 00:00:00' AT TIME ZONE 'Asia/Kolkata')*1000

-- relative boundary (start of today in the org timezone)
date_part('epoch', date_trunc('day', NOW() AT TIME ZONE 'Asia/Kolkata') AT TIME ZONE 'Asia/Kolkata')*1000
```

The relative form needs `AT TIME ZONE` twice; with only one, the boundary silently lands at 05:30 IST instead of midnight.

For grouping and display, use `date_trunc('day', to_timestamp(field/1000) AT TIME ZONE 'Asia/Kolkata')`. Avoid `DATE(...)`, interval-offset timezone tricks, and selecting raw epoch values in result columns.

Fields whose `data_type` is `'date'` are stored as UTC-midnight epochs: build the same epoch boundaries but without `AT TIME ZONE`, and display them with `to_timestamp(field/1000)::date`.

### Relations

Dot notation (`owner.name`) is always safe in `SELECT`, `WHERE`, and `GROUP BY`. Most objects carry `id`, `name`, `created_at`, `updated_at`, `owner`, `created_by`, and `updated_by`; confirm the rest with `describe_object`.

One caution: `IN` subqueries (in either direction) fail on columns whose `relationship_type` is `'virtual'`. The failure is silent, the subquery yields raw NULLs and the query returns 0 rows with no error. Use dot notation or a correlated `EXISTS` for those (see [Working with Relationships](#working-with-relationships)).

### Multi-select fields

Multi-select values are arrays. Use array operators, not string functions:

| Intent      | Expression                  |
| ----------- | --------------------------- |
| has any of  | `field && ARRAY['A','B']`   |
| has none of | `NOT (field && ARRAY['A'])` |
| is empty    | `field = ARRAY[NULL]`       |

`array_length` and array subscripts are not supported.

### Supported SQL surface

`JOIN`, `UNION`, CTEs (`WITH`), `EXTRACT`, `FILTER (WHERE ...)`, casts other than `::date`, and `pg_*` system references are not supported. The available functions are: `count`, `sum`, `avg`, `min`, `max`, `lower`, `upper`, `concat`, `trim`, `abs`, `round`, `coalesce`, `date_trunc`, `to_timestamp`, `date_part`, `if`, `case`. For conditional counts, use `COUNT(CASE WHEN condition THEN 1 END)`.

Style: keep identifiers bare (never double-quote them), quote strings with single quotes, use `ILIKE` for case-insensitive matching, and write booleans as `0`/`1`. Do not add `is_deleted` filters; deletion handling is automatic.

### Currency units

Convert amounts inside the SQL so results come back ready to display: `ROUND(SUM(amount)/10000000, 2)` for crore, `ROUND(SUM(amount)/100000, 2)` for lakh.

## Good Query Patterns

### Field selection

```sql theme={null}
SELECT id AS id, name AS name, created_at AS created_at
FROM account
ORDER BY created_at DESC
LIMIT 100
```

### Count by owner

```sql theme={null}
SELECT owner.name AS owner_name, COUNT(id) AS total_count
FROM deal
WHERE created_at >= date_part('epoch', TIMESTAMP '2025-03-01 00:00:00' AT TIME ZONE 'Asia/Kolkata')*1000
GROUP BY owner.name
ORDER BY total_count DESC
LIMIT 100
```

### Date grouping

```sql theme={null}
SELECT date_trunc('day', to_timestamp(created_at / 1000.0)) AS created_day,
       COUNT(id) AS record_count
FROM account
GROUP BY created_day
ORDER BY created_day ASC
LIMIT 100
```

## Unsupported Query Patterns

```sql theme={null}
-- JOINs are not supported
SELECT * FROM deal d JOIN account a ON a.id = d.account_id;

-- CTEs are not supported
WITH recent AS (SELECT * FROM account) SELECT * FROM recent;

-- Missing alias for aggregate
SELECT COUNT(id) FROM account;

-- Date strings are incorrect; use epoch milliseconds
SELECT * FROM account WHERE created_at >= '2025-03-25';
```

## Working with Relationships

Superleap objects reference each other through relation fields. There are two kinds - **direct** (to-one) and **virtual** (one-to-many) - and each supports a few query styles. Pick based on what you are computing.

### Direct (to-one) relations

A lookup field on the row itself (`owner`, `account`): each row points to at most one related record. Dot notation reads or filters the related record, and hops can be chained:

```sql theme={null}
SELECT id AS id, owner.name AS owner_name
FROM opportunity
WHERE owner.manager.email = 'lead@example.com'
LIMIT 100
```

<Tip>
  When filtering on a related record you already know by ID, compare the relation field itself (`WHERE stage_record = '<id>'`) rather than a field on it (`WHERE stage_record.name = 'Open'`). The ID form skips a join and is noticeably faster on large objects.
</Tip>

### Virtual (one-to-many) relations

A reverse link from a parent to its children (`associated_opportunities` on `lead`). Three supported ways to use them:

**1. Dot notation** - filter parents by a child field:

```sql theme={null}
SELECT COUNT(DISTINCT id) AS lead_count
FROM lead
WHERE associated_opportunities.stage = 'Open'
LIMIT 100
```

The underlying join returns one row per matching child, so a parent with three matching children appears three times. Use `COUNT(DISTINCT id)` for counts and `SELECT DISTINCT id` for row lists whenever a virtual relation appears in the query.

**2. Correlated `EXISTS` / `NOT EXISTS`** - a semi-join with no fan-out, so plain `COUNT(id)` is fine. Recommended for "has none" checks and for combining conditions across two different virtual relations:

```sql theme={null}
SELECT COUNT(id) AS lead_count
FROM lead
WHERE EXISTS (SELECT 1 FROM opportunity WHERE stage = 'Open' AND associated_lead = lead.id)
   OR NOT EXISTS (SELECT 1 FROM task WHERE associated_lead = lead.id)
LIMIT 100
```

**3. Querying the child object and climbing up** - when the child object is much smaller than the parent, start `FROM` the child; every hop up is to-one, so there is no fan-out and the scan is smaller:

```sql theme={null}
SELECT COUNT(DISTINCT associated_lead) AS lead_count,
       COUNT(DISTINCT CASE WHEN associated_lead.stage = 'New' THEN associated_lead END) AS new
FROM opportunity
WHERE stage = 'Open'
LIMIT 100
```

### Recommendations for virtual relations

* To check that a parent has **no** children, prefer `NOT EXISTS` (or count the complement: `|no child| = |all| − |has child|`) over `IS NULL` on the relation field - the explicit forms are unambiguous about "no child rows exist".
* To combine conditions on **two different** virtual relations with `OR`, prefer `OR` of two `EXISTS` subqueries, or split into one query per relation and merge with inclusion-exclusion (below). A single dot-notation `OR` across two virtual relations is expensive on large objects and may hit the execution timeout.
* `AND` across two virtual relations in dot notation is fine.

## Performance Patterns

### Many metrics in one scan

Compute related metrics as conditional aggregates in a single pass instead of one query per metric - the scan dominates the cost, extra CASE columns are nearly free:

```sql theme={null}
SELECT COUNT(DISTINCT id) AS total,
       COUNT(DISTINCT CASE WHEN stage = 'New' THEN id END) AS new,
       COUNT(DISTINCT CASE WHEN next_follow_up_at < date_part('epoch', date_trunc('day', NOW() AT TIME ZONE 'Asia/Kolkata') AT TIME ZONE 'Asia/Kolkata')*1000 THEN id END) AS overdue
FROM lead
WHERE associated_opportunities.stage = 'Open'
LIMIT 100
```

### Capped counts with LIMIT

`SELECT id ... LIMIT N` stops scanning once N rows are found. Paired with the [count endpoint](/api-reference/reports/count), this answers "at least N?" quickly on huge cohorts: if the returned count equals the limit, show "N+"; if below, it is already the exact count. Useful for dashboards where big tiles can display a capped value with an on-demand exact count.

### Unions via inclusion-exclusion

For counts across two virtual relations, running the parts separately and combining arithmetically is often the fastest reliable approach:

```
|A ∪ B|       = |A| + |B| − |A ∧ B|
|has neither| = |all| − |has A| − |has B| + |has both|
```

Each part is a simple query the engine executes efficiently, and the arithmetic composes per metric column.

### General tips

* Long queries are terminated at the execution timeout - decompose work so every query finishes with margin, and retry transient failures with backoff.
* Keep concurrent heavy analytics queries from one client low (around 2); the replica slows under parallel heavy scans.
* All date fields are epoch ms bigint (rule 2). `data_type 'date'` fields are stored as UTC-midnight epochs - use epoch boundaries WITHOUT `AT TIME ZONE` for those.

## Response

<ResponseField name="success" type="boolean">
  `true` when the request succeeds.
</ResponseField>

<ResponseField name="data" type="object">
  Execute result payload.

  <Expandable title="data fields">
    <ResponseField name="records" type="array">
      Query result rows. Each row is an object keyed by the aliases selected in the query.
    </ResponseField>

    <ResponseField name="message" type="string">
      Optional server message. For example, restricted object access can return an empty record set with a message.
    </ResponseField>
  </Expandable>
</ResponseField>

## Example Request

```bash theme={null}
curl --location 'https://<domain>.superleap.com/api/v1/reports/execute' \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer SUPERSECRETREDACTEDKEY' \
  --data @- <<'JSON'
{
  "query": "SELECT COUNT(id) AS record_count FROM account WHERE created_at >= date_part('epoch', TIMESTAMP '2026-06-03 00:00:00' AT TIME ZONE 'Asia/Kolkata')*1000 AND created_at < date_part('epoch', TIMESTAMP '2026-06-04 00:00:00' AT TIME ZONE 'Asia/Kolkata')*1000 LIMIT 100",
  "slug": "account"
}
JSON
```

## Example Response

```json theme={null}
{
  "success": true,
  "data": {
    "records": [
      {
        "record_count": 3080
      }
    ]
  }
}
```

## Error Responses

```json 400 theme={null}
{
  "success": false,
  "data": {
    "message": "Invalid slug",
    "status_code": 400
  }
}
```

```json 429 theme={null}
{
  "success": false,
  "data": {
    "message": "Too many concurrent analytics requests. Please try again later.",
    "status_code": 429
  }
}
```

```json 504 theme={null}
{
  "success": false,
  "data": {
    "message": "query execution timed out after 20 seconds",
    "status_code": 504
  }
}
```