Document Database

Overview

Document Database - Store and query any data type with a flexible, schemaless JSON document database. Documents are JSON structures which allow any arbitrary fields, and documents are grouped under collections.

Operations

An operation is an action performed on a collection, such as inserting, querying, updating, or deleting documents. All database operations require the collection property to specify which collection to operate on. The operation type is specified in the op property.

Operation	Description
db.insert	Insert new documents into a collection
db.find	Query and retrieve documents from a collection
db.count	Count documents matching filter criteria
db.update	Modify existing documents in a collection
db.upsert	Update existing documents or insert if not found
db.delete	Soft delete documents (recoverable for 24 hours)
db.archive	Archive documents to hide from queries
db.restore	Restore deleted or archived documents

---

Data Structure

• Database → Collection → Document

System Fields

Every document automatically includes these system-managed fields:

• _key - Primary key (UUID, auto-generated, unique identifier)
• _created_at - Timestamp when the document was created (ISO 8601 format)
• _modified_at - Timestamp when the document was last updated (ISO 8601 format)

These fields are read-only and maintained automatically by the system.

Create Collections

Collections must be created prior to adding documents.

Go to Singlebase Console to create and manage your collections.

---

Filter Criteria

Filter criteria are used to match documents in read, update, delete, and archive operations. Operators are placed in the key of the property using the :$ syntax.

Basic syntax:

{
  "field:$operator": "value",
  "nested.path.to.field:$operator": "value"
}

Simple equality (shorthand):

{
  "field": "value",
  "status": "active"
}

Filter Syntax Styles

Flat syntax (with dot notation):

{
  "user.profile.age:$gte": 18,
  "status": "active"
}

Nested syntax (with nested objects):

{
  "user": {
    "profile": {
      "age:$gte": 18
    }
  }
}

Both syntaxes are equivalent. The operator is always attached to the field name with a colon.

Filter Operators

Common operators:

• $eq / $ne - Equal to / Not equal to
• $gt / $gte / $lt / $lte - Comparison operators
• $in / $nin - Value in/not in array
• $includes / $nincludes - Array includes/doesn't include value
• $between - Value between range

For a complete reference of all filter operators, see the Filter Operators page.

---

Create Operations

Create or insert operations add new documents to a collection. A collection must exist before documents can be added.

db.insert

Insert one or multiple new documents into a collection. System fields _key, _created_at, and _modified_at are automatically generated.

Single document:

{
  "op": "db.insert",
  "collection": "users",
  "data": {
    "name": "Jane Smith",
    "email": "jane@example.com"
  }
}

Multiple documents:

{
  "op": "db.insert",
  "collection": "products",
  "data": [
    { "name": "Laptop", "price": 999.99 },
    { "name": "Mouse", "price": 29.99 }
  ]
}

Response:

{
  "data": [
    {
      "_key": "auto_generated_123",
      "_created_at": "2024-01-15T10:30:00Z",
      "_modified_at": "2024-01-15T10:30:00Z",
      "name": "Jane Smith",
      "email": "jane@example.com"
    }
  ]
}

---

Read Operations

Read operations retrieve documents from a collection. They use filter criteria to match documents (see Filter Criteria section).

db.find

Find one or multiple documents by criteria. Supports filtering, sorting, pagination, Output Expressions, and joining with other collections.

{
  "op": "db.find",
  "collection": "users",
  "filter": {
    "status": "active",
    "age:$gte": 18
  },
  "sort": "_created_at desc",
  "limit": 50
}

Advanced features:

• Output Expressions - Reshape results (see Output Expressions)
• Lookup - Join with other collections (see Lookup)
• Pagination - Control results with limit/offset or page/per_page (see Limit/Offset & Sorting)

Response:

{
  "data": [
    {
      "_key": "user_123",
      "_created_at": "2024-01-15T10:30:00Z",
      "_modified_at": "2024-01-15T10:30:00Z",
      "name": "John Doe",
      "status": "active",
      "age": 25
    }
  ],
  "meta": {
    "pagination": {
      "total_pages": 3,
      "has_next": true,
      "count": 50
    }
  }
}

db.count

Count the number of documents matching the filter criteria. Returns only the count, not the documents themselves. Uses the same filter criteria as db.find.

{
  "op": "db.count",
  "collection": "users",
  "filter": { 
    "status": "active",
    "age:$gte": 18
  }
}

Response:

{
  "data": { "count": 1247 }
}

---

Update Operations

Update operations modify existing documents in a collection. They use the same filter criteria as read operations (see Filter Criteria section).

db.update

Update existing documents in a collection. Can update by document key or by filter criteria. Updates _modified_at automatically.

Note: When _key is provided in the data, only that specific document will be updated and other filter criteria will be ignored.

By key:

{
  "op": "db.update",
  "collection": "users",
  "data": {
    "_key": "user_123",
    "status": "inactive"
  }
}

By filter:

{
  "op": "db.update",
  "collection": "orders",
  "filter": {
    "status": "pending",
    "_created_at:$lt": "2024-01-01T00:00:00Z"
  },
  "data": {
    "status": "expired"
  }
}

Response:

{
  "data": [
    {
      "_key": "user_123",
      "_modified_at": "2024-01-15T10:30:00Z",
      "status": "inactive"
    }
  ]
}

Note: For advanced data modifications using Data Operators and helper functions, see the Data Modifiers section.

---

db.upsert

Update existing documents or insert new ones if no matches are found. Useful for maintaining unique records without checking existence first. Uses the same filter criteria as read operations.

{
  "op": "db.upsert",
  "collection": "preferences",
  "filter": { "user_id": "user_123" },
  "update": { "theme": "dark" },
  "insert": { "user_id": "user_123", "theme": "light" }
}

---

Delete Operations

Delete Operations cover: Delete, Archive and Restore.

Delete operations remove documents from a collection. Deleted documents can be recovered within 24 hours, after which they are permanently deleted.

Archive & Restore operations remove documents from being queried. Archived documents are not deleted, but they can't be modified.

These operations use the same filter criteria as read operations (see Filter Criteria section).

db.delete

Soft delete documents matching the filter criteria. Documents can be recovered using the returned reference ID within 24 hours. After 24 hours, documents are permanently deleted.

Note: When _key is provided in the filter, only that specific document will be deleted and other filter criteria will be ignored.

{
  "op": "db.delete",
  "collection": "users",
  "filter": { 
    "status": "inactive",
    "last_login:$lt": "2023-01-01T00:00:00Z"
  }
}

Response:

{
  "data": { "_ref": "del_ref_abc123" }
}

Save _ref to restore within 24 hours.

db.archive

Archive documents to hide them from normal queries while keeping them in storage. Archived documents don't appear in find operations but can be restored.

Note: When _key is provided in the filter, only that specific document will be archived and other filter criteria will be ignored.

{
  "op": "db.archive",
  "collection": "orders",
  "filter": { 
    "status": "completed",
    "_created_at:$lt": "2023-01-01T00:00:00Z"
  }
}

Response:

{
  "data": { "_ref": "arch_ref_def456" }
}

db.restore

Restore previously deleted or archived documents using their reference ID. Brings documents back to active state.

{
  "op": "db.restore",
  "collection": "users",
  "filter": { "_ref": "del_ref_abc123" }
}

Response:

{
  "data": { "restored": true }
}

---

Limit & Sorting

Control the number of results and their order using pagination and sorting parameters.

Pagination

Using limit/offset:

{
  "limit": 50,
  "offset": 0
}

Using page/per_page:

{
  "page": 1,
  "per_page": 50
}

Important: Pagination parameters must be used in pairs - use either limit/offset together or page/per_page together. You cannot mix limit with page or offset with per_page.

• limit / per_page - Maximum number of documents to return (default: 100)
• offset - Number of documents to skip (starts at 0)
• page - Page number (starts at 1)

Sorting

Sort results by one or more fields using the sort parameter:

{
  "sort": "_created_at desc"
}

Sort syntax:

• Single field: "field_name asc" or "field_name desc"
• Multiple fields: "field1 desc, field2 asc"
• Default order is descending if not specified

Common sort examples:

{ "sort": "_created_at desc" }
{ "sort": "name asc" }
{ "sort": "priority desc, _created_at desc" }

---

Lookup Expressions

Lookup performs a left outer join to combine documents from other collections during db.find operations. Joined data is added to a __refs__ property in the results.

Use cases:

• Combining related data from multiple collections
• Building complete data structures with relationships
• Avoiding multiple separate queries

Key features:

• Supports nested lookups (up to 6 levels deep)
• Recommended to use with Output Expressions to reshape results
• Uses special context variables: $root, $parent

Basic Syntax

{
  "lookup": {
    "aliasName": {
      "collection": "foreign_collection",
      "on": "$root.foreign_key"
    }
  }
}

Parameters:

• collection (required) - The foreign collection to join
• on (required) - The field to join on (supports $root, $parent context)
• op (optional) - Join operator (default: direct match, use "IN" for array matching)
• filter (optional) - Additional filter criteria for joined documents
• limit (optional) - Limit number of joined documents
• lookup (optional) - Nested lookup configuration

Simple Example

Join orders with user data:

{
  "op": "db.find",
  "collection": "orders",
  "filter": { "status": "completed" },
  "lookup": {
    "customer": {
      "collection": "users",
      "on": "$root.user_id"
    }
  }
}

Result structure:

{
  "data": [
    {
      "_key": "order_123",
      "user_id": "user_456",
      "status": "completed",
      "__refs__": {
        "customer": {
          "_key": "user_456",
          "name": "John Doe",
          "email": "john@example.com"
        }
      }
    }
  ]
}

Advanced Example with Nested Lookups

{
  "op": "db.find",
  "collection": "orders",
  "filter": { "status": "completed" },
  "lookup": {
    "customer": {
      "collection": "users",
      "on": "$root.user_id"
    },
    "items": {
      "collection": "order_items",
      "on": "$root._key",
      "lookup": {
        "product": {
          "collection": "products",
          "on": "$parent.product_id"
        }
      }
    },
    "assignedUsers": {
      "collection": "users",
      "on": "$root.assigned_user_ids",
      "op": "IN"
    }
  }
}

Using Lookup with Output Expressions

Combine lookup with Output Expressions to create clean response structures:

{
  "op": "db.find",
  "collection": "orders",
  "filter": { "status": "completed" },
  "lookup": {
    "customer": {
      "collection": "users",
      "on": "$root.user_id"
    }
  },
  "output": {
    "orderId": "{{ _key }}",
    "status": "{{ status }}",
    "customerName": "{{ __refs__.customer.name }}",
    "customerEmail": "{{ __refs__.customer.email }}"
  }
}

Result:

{
  "data": [
    {
      "orderId": "order_123",
      "status": "completed",
      "customerName": "John Doe",
      "customerEmail": "john@example.com"
    }
  ]
}

---

Output Expressions

Transform and reshape query results using output expressions. Create custom response structures, compute derived values, and format data for different views.

Basic output:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "$name": "name",
    "$email": "email"
  }
}

Output with expressions:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "display_name": "{{ first_name }} {{ last_name }}",
    "contact": "{{ email }}",
    "$age": "age",
    "is_adult": "?{{ age >= 18 }}",
    "$order_count": "@len(orders)"
  }
}

Output Expressions

Output supports three expression types:

• "{{ expression }}" - Template expression (returns string)
• "?" - Native expression (returns original data type)
• "$field" - Shorthand for native type (equivalent to ?)

Example:

{
  "op": "db.find",
  "collection": "products",
  "output": {
    "title": "{{ name }}",
    "price_with_tax": "?{{ price * 1.08 }}",
    "$in_stock": "stock > 0",
    "display": "{{ name }} - ${{ price }}"
  }
}

Input data:

{
  "name": "Widget",
  "price": 29.99,
  "stock": 15
}

Output result:

{
  "title": "Widget",
  "price_with_tax": 32.39,
  "in_stock": true,
  "display": "Widget - $29.99"
}

Mapping Arrays

Use :$map(field) to transform array items:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "$name": "name",
    "orders:$map(orders)": {
      "$total": "total",
      "status_display": "{{ status }}",
      "is_completed": "?{{ status == 'completed' }}"
    }
  }
}

Input data:

{
  "name": "John Doe",
  "orders": [
    { "total": 100, "status": "completed" },
    { "total": 150, "status": "pending" }
  ]
}

Output result:

{
  "name": "John Doe",
  "orders": [
    {
      "total": 100,
      "status_display": "completed",
      "is_completed": true
    },
    {
      "total": 150,
      "status_display": "pending",
      "is_completed": false
    }
  ]
}

Field Selection

Include specific fields:

{
  "op": "db.find",
  "collection": "users",
  "output": "{name, email, age}"
}

Rename specific fields:

{
  "op": "db.find",
  "collection": "users",
  "output": "{myNewName: name, myNewEmail:email, age}"
}

Exclude specific fields:

{
  "op": "db.find",
  "collection": "users",
  "output": "!{password, ssn, internal_notes}"
}

Include all fields using **:

{
  "op": "db.find",
  "collection": "users",
  "output": "**"
}

{
  "output": {
    "_": "**"
  }
}

Include all fields and other ones:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "_": "**",
    "$fullname": "first_name + ' ' + last_name"
  }
}

Common Use Cases

Create API responses:

{
  "op": "db.find",
  "collection": "products",
  "output": {
    "$id": "_key",
    "$name": "name",
    "$price": "price",
    "sale_price": "?{{ price * 0.9 }}",
    "display": "{{ name }} - ${{ price }}"
  }
}

Format user profiles:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "display_name": "{{ first_name }} {{ last_name }}",
    "contact": "{{ email }} | {{ phone }}",
    "$is_verified": "email_verified && phone_verified",
    "member_since": "{{ @formatdate(_created_at, 'YYYY-MM-DD') }}"
  }
}

Calculate summaries:

{
  "op": "db.find",
  "collection": "orders",
  "output": {
    "$order_id": "_key",
    "$subtotal": "@sum(@map(items, 'price * quantity'))",
    "tax": "?{{ subtotal * 0.08 }}",
    "total": "?{{ subtotal + (subtotal * 0.08) }}",
    "$item_count": "@len(items)"
  }
}

Transform nested data:

{
  "op": "db.find",
  "collection": "users",
  "output": {
    "$name": "name",
    "$city": "address.city",
    "$country": "address.country",
    "full_address": "{{ address.street }}, {{ address.city }}, {{ address.zip }}"
  }
}

Output Expressions provides powerful data reshaping capabilities with template expressions and helper functions.

---

Data Modifiers

Data Modifiers provide a declarative way to transform and update data structures in update operations. They combine Data Operators and helper functions to make data modifications seamless.

For complete references, see Data Operators and Helper Functions.

Data Operators

Data Operators modify data in place using the :$operator syntax in field names.

Auto-assign operators:

{
  "created_at:$now": true,
  "user_id:$uuid4": true,
  "timestamp:$timestamp": true
}

Value modification:

{
  "score:$incr": 10,
  "credits:$decr": 5,
  "status:$set": "active"
}

List operations:

{
  "tags:$push": "premium",
  "roles:$extend": ["admin", "moderator"],
  "followers:$addset": ["user_123", "user_456"]
}

Common operators:

• $set, $replace - Set/replace values
• $incr, $decr - Increment/decrement numbers
• $now, $datetime, $timestamp - Date/time values
• $uuid, $uuid4, $uuid7 - Generate IDs
• $push, $extend, $addset - List operations
• $rename, $copy, $template - Data operations

Helper Functions

Helper functions are template-based functions used in expressions, prefixed with @:

{
  "fullName": "{{ @concat(firstName, ' ', lastName) }}",
  "userCount": "{{ @len(users) }}",
  "formattedDate": "{{ @formatdate(created_at, 'YYYY-MM-DD') }}",
  "adminCount": "{{ @len(@filter(users, 'role == \"admin\"')) }}"
}

Combined Example

{
  "op": "db.update",
  "collection": "users",
  "filter": { "_key": "user_123" },
  "data": {
    "name": "Jane Doe",
    "score:$incr": 10,
    "tags:$push": "premium",
    "updated_at:$now": true,
    "session_id:$uuid4": true,
    "greeting": "Hello {{ name }}!",
    "totalOrders": "{{ @len(orders) }}"
  }
}

---

Error Handling

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Invalid collection name"
  }
}

Common codes: AUTHENTICATION_FAILED, COLLECTION_NOT_FOUND, VALIDATION_ERROR, RATE_LIMIT_EXCEEDED