Canonical page: https://docs.singlebase.io/queries

# Query Syntax

Select documents with `filter`, then sort, paginate, and search the results.

## Overview

**Filters** are JSON objects that describe which documents to match. The same syntax works everywhere a `filter` is accepted.

**Where filters work:**
- `data.query`, `data.count`, `data.aggregate`, `data.update`, `data.upsert`, `data.archive`, `data.delete`
- `files.query` (fields belong to [file records](/files.md#files-query))
- `users.query` (fields must start with `data.`, see [Users](/users.md#users-query))

**Key features:**
- Bare values mean equality
- Inline operators: `"price:$gte": 10`
- Comparison, array, string, type, and logical operators
- Shortcuts for dates, missing values, and empty arrays or strings
- Page-based or offset-based pagination

**Example:**
```json
{
  "operation": "data.query",
  "payload": {
    "collection": "products",
    "filter": {
      "status": "active",
      "price:$between": [10, 100],
      "tags:$includes": "sale"
    },
    "sort": "price asc",
    "per_page": 20
  }
}
```

---

## Filter Syntax

Multiple conditions are joined with AND.

```json
{
  "status": "active",
  "stats.views:$gte": 10,
  "tags:$includes": "api"
}
```

Write an operator inline as `field:$operator`, or as a nested object. These are equivalent:

```json
{ "price:$gte": 10, "price:$lt": 100 }
```

```json
{ "price": { "$gte": 10, "$lt": 100 } }
```

**Field paths:**
- Dot notation for nested fields: `profile.city`
- `[]` for array traversal: `people[].name`. `[*]` is not valid

---

## Complete Operator Index

The following operators and shortcuts are accepted by `lib_kokoa.py`. Names are case-sensitive. Use `field:$operator` or a nested operator object, except logical operators, which belong at filter level.

| Group | Supported names |
|---|---|
| Equality | `$eq`, `$ne` |
| Ordered comparison | `$gt`, `$gte`, `$lt`, `$lte`, `$between` |
| Presence and type | `$exists`, `$type` |
| Membership | `$in`, `$nin`, `$includes`, `$nincludes`, `$all`, `$any`, `$none` |
| Arrays | `$elemMatch`, `$size` |
| Strings | `$startsWith`, `$endsWith`, `$contains`, `$ilike`, `$istartsWith`, `$iendsWith`, `$icontains`, `$regex` |
| Logical | `$and`, `$or`, `$nor`, `$not` |
| Date aliases | `$before`, `$after`, `$since`, `$until` |
| Missing and empty values | `$orMissing`, `$nex`, `$empty`, `$blank` |
| Range complement | `$outside` |

### Nested arrays and missing values

```json
{
  "operation": "data.query",
  "payload": {
    "collection": "orders",
    "filter": {
      "items:$elemMatch": {"sku:$istartsWith": "bag-", "qty:$gte": 2},
      "tags:$none": ["internal", "test"],
      "discount:$orMissing": {"$lte": 10},
      "summary:$blank": false,
      "price:$outside": [20, 50]
    }
  }
}
```

`$blank` checks the empty string, not whitespace. `$outside` uses strict comparisons beyond the two bounds. `$orMissing` also accepts a bare scalar, which means equality or absent/null. Use `$elemMatch` when several conditions must apply to the same array element; `people[].name` traverses an array field.

## Comparison and Presence

| Operator | Matches | Example |
|---|---|---|
| `$eq` | Equal value | `"status:$eq": "active"` |
| `$ne` | Present and different | `"status:$ne": "draft"` |
| `$gt`, `$gte` | Greater than / greater than or equal | `"price:$gte": 10` |
| `$lt`, `$lte` | Less than / less than or equal | `"price:$lt": 100` |
| `$between` | Inclusive range | `"price:$between": [10, 100]` |
| `$exists` | Present (`true`) or absent/null (`false`) | `"summary:$exists": true` |
| `$type` | Value type | `"price:$type": "number"` |

**Rules:**
- Ordered comparisons accept numbers or strings, not booleans.
- `$between` takes exactly two bounds.
- `$ne` only matches documents where the field is present. Use `$nex` to include missing fields.
- `$type` accepts `number`, `boolean`, `string`, `array`, `object`, `null`, `integer`, `real`, `text`, `true`, and `false`.

---

## Arrays and Membership

| Operator | Matches | Example |
|---|---|---|
| `$in` | Value is in the list | `"status:$in": ["active", "draft"]` |
| `$nin` | Value is not in the list | `"status:$nin": ["deleted"]` |
| `$includes` | Array contains the value | `"tags:$includes": "api"` |
| `$nincludes` | Array does not contain the value | `"tags:$nincludes": "internal"` |
| `$all` | Array contains every listed value | `"tags:$all": ["api", "docs"]` |
| `$any` | Array contains at least one listed value | `"tags:$any": ["api", "docs"]` |
| `$none` | Array contains none of the listed values | `"tags:$none": ["internal", "draft"]` |
| `$size` | Array length | `"tags:$size": {"$gte": 2}` |
| `$elemMatch` | One array element matches a filter | `"items:$elemMatch": {"sku": "A1", "qty:$gte": 2}` |

**Rules:**
- List operands must not be empty.
- `$size` accepts an integer, or an object using `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, and `$lte`.

---

## Strings

| Operator | Matches | Example |
|---|---|---|
| `$startsWith` | Prefix | `"sku:$startsWith": "A-"` |
| `$endsWith` | Suffix | `"email:$endsWith": "@example.com"` |
| `$contains` | Substring | `"title:$contains": "API"` |
| `$istartsWith`, `$iendsWith`, `$icontains` | Case-insensitive versions | `"title:$icontains": "api"` |
| `$ilike` | Case-insensitive LIKE pattern | `"title:$ilike": "%api%"` |
| `$regex` | Regular expression | `"slug:$regex": "^release-[0-9]+$"` |

`$ilike` keeps `%` and `_` as LIKE wildcards. Invalid regular expressions are rejected.

---

## Logical Operators

Use `$and`, `$or`, `$nor`, and `$not` at the filter level.

```json
{
  "operation": "data.query",
  "payload": {
    "collection": "notes",
    "filter": {
      "status": "active",
      "$or": [
        { "tags:$includes": "api" },
        { "stats.views:$gte": 100 }
      ],
      "$not": { "title:$startsWith": "Internal" }
    }
  }
}
```

| Operator | Takes | Matches when |
|---|---|---|
| `$and` | Non-empty array of filters | Every filter matches |
| `$or` | Non-empty array of filters | At least one filter matches |
| `$nor` | Non-empty array of filters | No filter matches |
| `$not` | One filter object | The filter does not match |

All top-level conditions still apply. Logical operators cannot be placed inside a field's operator object.

---

## Shortcuts

Shortcuts expand into standard filters.

| Shortcut | Meaning | Example |
|---|---|---|
| `$before`, `$after` | `$lt`, `$gt` | `"_created_at:$before": "2026-01-01"` |
| `$since`, `$until` | `$gte`, `$lte` | `"_created_at:$since": "2026-01-01"` |
| `$nex` | Not equal, or absent/null | `"plan:$nex": "free"` |
| `$orMissing` | Matches the condition, or absent/null | `"price:$orMissing": {"$lt": 100}` |
| `$empty` | `true`: array absent, null, or empty. `false`: present and non-empty | `"tags:$empty": true` |
| `$blank` | `true`: string absent, null, or `""`. `false`: present and non-empty | `"summary:$blank": true` |
| `$outside` | Below or above an inclusive range | `"price:$outside": [10, 100]` |

`$orMissing` cannot contain another shortcut that expands into a logical clause.

**Example - Documents created this year that are not marked free:**
```json
{
  "filter": {
    "_created_at:$since": "2026-01-01",
    "plan:$nex": "free"
  }
}
```

---

## Sorting

Pass `sort` to `data.query` as `"<field> asc"` or `"<field> desc"`. The default is `_created_at desc`.

```json
{
  "operation": "data.query",
  "payload": {
    "collection": "products",
    "sort": "price asc"
  }
}
```

---

## Pagination

Use page-based or offset-based pagination. Pick one style per request.

**Page-based:**
```json
{
  "operation": "data.query",
  "payload": {
    "collection": "notes",
    "page": 2,
    "per_page": 20
  }
}
```

**Offset-based:**
```json
{
  "operation": "data.query",
  "payload": {
    "collection": "notes",
    "limit": 20,
    "offset": 20
  }
}
```

Pages start at 1. `data.query` defaults to `page: 1` and `per_page: 20`.

**Pagination metadata:**
```json
{
  "meta": {
    "pagination": {
      "total_items": 84,
      "count": 20,
      "per_page": 20,
      "page": 2,
      "total_pages": 5,
      "next_page": 3,
      "prev_page": 1
    }
  }
}
```

**Loop through documents with the JavaScript SDK:**

The SDK returns `data` directly, so this offset loop stops when a batch is shorter than the requested limit. For a stable traversal, avoid concurrent inserts/deletes and choose a deterministic sort for your data. See [JavaScript SDK](/sdk-javascript.md).

```javascript
const all = [];
const limit = 100;
for (let offset = 0; ; offset += limit) {
  const documents = await sbc.data.query({
    collection: "notes", limit, offset, sort: "_id asc",
  });
  all.push(...documents);
  if (documents.length < limit) break;
}
```

**Other operations:**

| Operation | Pagination |
|---|---|
| `files.query` | `page`/`per_page` (defaults `1` and `20`) or `limit`/`offset` |
| `users.query` | `page`/`per_page` or `limit`/`offset`, no API defaults |
| `chat.list` | `limit`/`offset` only |

---

## Search

Use `search` with `data.query` for full-text search. Combine it with `filter` to narrow the matches.

```json
{
  "operation": "data.query",
  "payload": {
    "collection": "articles",
    "search": "API documentation",
    "filter": { "status": "published" },
    "per_page": 10
  }
}
```

Full-text search depends on the collection's configuration. To match text in one field, use a string operator such as `$icontains`.

---

## Next Steps

- [Data](/data.md) - Query, update, and archive documents
- [Templates](/templates.md) - Compute values on write
- [Files](/files.md) - Query file records