SapixDBSapixDB/Docs
Home

Manual · Querying

Null & Existence Operators

SapixDB filters support 15 operators covering equality, comparison, string matching, set membership, full-text search, and field existence. This page is the complete operator reference.

Filter expression shape

Every leaf filter is a JSON object with three keys. Operators that do not require a value (is_null, is_not_null) omit the value key.

JSON
{
  "field": "plan",
  "op":    "eq",
  "value": "pro"
}

Leaf filters can be combined with AND, OR, and NOT logical wrappers:

JSON
{
  "AND": [
    {"field": "plan",   "op": "eq",  "value": "pro"},
    {"field": "active", "op": "eq",  "value": true}
  ]
}

All 15 operators

OperatorMeaningExample valueValue required
eqEqual"pro"yes
neNot equal"free"yes
gtGreater than100yes
ltLess than500yes
gteGreater than or equal0yes
lteLess than or equal999yes
betweenInclusive range[10, 50]yes (array of 2)
containsSubstring match"@gmail"yes
starts_withPrefix match"usr_"yes
ends_withSuffix match".com"yes
likeSQL-style wildcard (%, _)"usr_%"yes
inValue in list["free", "starter"]yes (array)
is_nullField is null or missing—no
is_not_nullField is present and not null—no
ftsFull-text search (tokenized)"database agent"yes

is_null — field is missing or null

⚠ is_null matches both JSON null and absent fieldsA record where phone is explicitly set to null and a record where phone is simply not present are both matched by is_null. SapixDB treats absence and null as equivalent for the purpose of existence checks.

Find all users whose phone field is missing or null — useful for finding incomplete profile records.

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":   "scan",
    "limit":  50,
    "filter": {
      "field": "phone",
      "op":    "is_null"
    }
  }' | python3 -m json.tool

is_not_null — field is present and not null

The inverse of is_null. Use it to filter to records that actually have a value for a given field.

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":   "scan",
    "limit":  50,
    "filter": {
      "field": "phone",
      "op":    "is_not_null"
    }
  }' | python3 -m json.tool

Neither is_null nor is_not_null accepts a value key. Including one will cause a validation error.

between — inclusive numeric range

The value must be a two-element array [min, max]. Both bounds are inclusive. Works on numbers and on ISO-8601 date strings.

curl — amount between 100 and 500
curl -s -X POST http://localhost:7475/v1/agents/orders/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":   "scan",
    "limit":  50,
    "filter": {
      "field": "amount",
      "op":    "between",
      "value": [100, 500]
    }
  }' | python3 -m json.tool
curl — date range
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":   "scan",
    "limit":  200,
    "filter": {
      "field": "created_at",
      "op":    "between",
      "value": ["2026-06-01", "2026-06-30"]
    }
  }' | python3 -m json.tool

in — value in a list

Equivalent to multiple eq conditions joined with OR, but more concise. The value must be a non-empty array.

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":   "scan",
    "limit":  50,
    "filter": {
      "field": "plan",
      "op":    "in",
      "value": ["pro", "enterprise"]
    }
  }' | python3 -m json.tool

like — SQL-style wildcard

Supports the two standard SQL wildcard characters: % matches any sequence of characters (including none), and _ matches exactly one character. Matching is case-sensitive.

curl — IDs starting with usr_ followed by any 26 chars
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":   "scan",
    "limit":  50,
    "filter": {
      "field": "user_id",
      "op":    "like",
      "value": "usr_%"
    }
  }' | python3 -m json.tool

starts_with and ends_with

Simpler alternatives to like when you only need a prefix or suffix match. They do not require escaping wildcard characters.

curl — starts_with
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":   "scan",
    "limit":  50,
    "filter": {
      "field": "user_id",
      "op":    "starts_with",
      "value": "usr_"
    }
  }' | python3 -m json.tool
curl — ends_with
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":   "scan",
    "limit":  50,
    "filter": {
      "field": "email",
      "op":    "ends_with",
      "value": ".edu"
    }
  }' | python3 -m json.tool

contains — substring match

Matches if the field value contains the given string anywhere within it.

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":   "scan",
    "limit":  50,
    "filter": {
      "field": "email",
      "op":    "contains",
      "value": "@gmail"
    }
  }' | python3 -m json.tool

fts — full-text search

Tokenizes the value and the stored field, then performs a token intersection match. Useful for natural language lookups on longer text fields such as descriptions, notes, or titles.

curl
curl -s -X POST http://localhost:7475/v1/agents/articles/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":   "scan",
    "limit":  20,
    "filter": {
      "field": "body",
      "op":    "fts",
      "value": "database agent"
    }
  }' | python3 -m json.tool
ℹ fts vs containsfts tokenizes both sides and checks for token overlap — it is order-insensitive and ignores punctuation. contains does a raw substring search — it is faster but requires the exact character sequence to appear in the field.

Combining operators with AND / OR / NOT

Any number of leaf filters can be composed into a tree using AND, OR, and NOT wrapper objects. Each wrapper takes an array value (AND, OR) or a single object value (NOT).

curl — paying users in the US with a phone on record
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":   "scan",
    "limit":  100,
    "filter": {
      "AND": [
        {"NOT": {"field": "plan",    "op": "eq",       "value": "free"}},
        {"field": "country",         "op": "eq",       "value": "US"},
        {"field": "phone",           "op": "is_not_null"}
      ]
    }
  }' | python3 -m json.tool