SapixDBSapixDB/Docs
Home

Manual · Querying

Distinct Query

Enumerate every unique value that appears in a given field across your agent's records — optionally scoped to a subset of records with a filter.

ℹ What distinct doesA distinct query scans the agent's records, extracts the value of one named field from each record, and returns a deduplicated, sorted list together with a total count of unique values. Null and missing field values are excluded from results.

Query shape

Send a POST to /v1/agents/:id/query with a JSON body using "type": "distinct".

JSON
{
  "type":   "distinct",
  "field":  "plan",
  "limit":  100,
  "filter": { ... }   // optional
}
ParameterTypeRequiredDescription
typestringyesAlways "distinct"
fieldstringyesThe field name whose unique values you want to enumerate
limitintegernoMaximum number of distinct values to return (default 100, max 10 000)
filterobjectnoStandard SapixDB filter expression applied before deduplication

Basic distinct — all unique plans

Find every value that appears in the plan field across all records in the users agent.

curl
curl -s -X POST http://localhost:7475/v1/agents/users/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":  "distinct",
    "field": "plan"
  }' | python3 -m json.tool
Response
{
  "distinct": {
    "field":  "plan",
    "values": ["enterprise", "free", "pro", "starter"],
    "count":  4
  }
}

Values are returned in ascending lexicographic order. The count field reflects the number of unique values in the values array — it does not count how many records have each value. For per-value record counts use the aggregate query type.

Distinct with filter

Add a filter to restrict which records are considered before deduplication. The following example finds all unique country values among paying users only (i.e. plan is not free).

curl
curl -s -X POST http://localhost:7475/v1/agents/users/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":  "distinct",
    "field": "country",
    "filter": {
      "NOT": {
        "field": "plan",
        "op":    "eq",
        "value": "free"
      }
    }
  }' | python3 -m json.tool
Response
{
  "distinct": {
    "field":  "country",
    "values": ["DE", "FR", "GB", "JP", "US"],
    "count":  5
  }
}

Any valid SapixDB filter expression — including AND, OR, NOT, comparisons, and substring operators — can be used as the filter value.

Response shape

JSON
{
  "distinct": {
    "field":  string,      // the field that was enumerated
    "values": any[],       // sorted, deduplicated values
    "count":  number       // length of the values array
  }
}
⚠ Null and missing valuesRecords where the target field is null or absent are silently skipped. If you need to count records with missing fields use the is_null filter operator.

Controlling result size with limit

By default, up to 100 unique values are returned. Raise the limit to collect all values from a high-cardinality field:

curl
curl -s -X POST http://localhost:7475/v1/agents/events/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":  "distinct",
    "field": "user_id",
    "limit": 10000
  }' | python3 -m json.tool
ℹ Maximum limitThe server caps limit at 10 000. For cardinalities beyond that, use an export or a chunked scan.

Use cases

Enumeration — populate a dropdown

Before rendering a filter UI, call distinct to discover which values actually exist in the data rather than hard-coding them. This keeps your UI automatically in sync as new values are written.

TypeScript
const res = await fetch(
  "http://localhost:7475/v1/agents/products/query",
  {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: "Bearer spx_root_YOUR_ROOT_KEY",
    },
    body: JSON.stringify({ type: "distinct", field: "category" }),
  }
);
const { distinct } = await res.json();
// distinct.values → ["electronics", "furniture", "lighting", ...]
const options = distinct.values.map((v: string) => ({ label: v, value: v }));

Validation — detect unexpected values

After an import or a migration, run distinct on enum-like fields to confirm only allowed values are present.

curl
# Expected: ["active", "churned", "trial"]
curl -s -X POST http://localhost:7475/v1/agents/subscriptions/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{"type": "distinct", "field": "status"}' \
  | python3 -c "
import sys, json
d = json.load(sys.stdin)
allowed = {'active', 'churned', 'trial'}
found   = set(d['distinct']['values'])
bad     = found - allowed
print('OK' if not bad else f'Unexpected values: {bad}')
"

Analytics — unique active countries this month

Combine distinct with a filter to scope enumeration to a time window or a cohort.

curl
curl -s -X POST http://localhost:7475/v1/agents/events/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":  "distinct",
    "field": "country",
    "filter": {
      "AND": [
        {"field": "event_type", "op": "eq",  "value": "login"},
        {"field": "month",      "op": "eq",  "value": "2026-06"}
      ]
    }
  }' | python3 -m json.tool

Distinct vs aggregate

NeedQuery type
List of unique valuesdistinct
Count of records per valueaggregate (group_by)
Sum / average of a numeric fieldaggregate
Whether a specific value existsscan + filter (limit 1)