Metadata Filtering Guide

Overview

LocalVectorDB provides a powerful and secure metadata filtering system that allows you to query documents based on their metadata fields. The filtering system supports MongoDB-style operators and generates safe parameterized SQL queries to prevent injection attacks.

Important

Metadata fields must be declared in the database’s metadata_schema to be stored and queryable. Each declared field becomes a real column on the documents table. Any key you pass in a document’s metadata that is not part of the schema is dropped on insert with a logged warning (it is not stored, and you cannot filter on it). Likewise, filtering on a field that is not in the schema raises an error. Declare every field you intend to store, filter, or sort on when you create the database (or add it later with update_metadata_schema()).

Key Features

• Secure by design - All queries use parameterized SQL

• MongoDB-style operators for intuitive querying

• Logical operators for complex conditions

• Type-safe operations with schema validation

• JSON/Array support for complex data structures

• Performance optimized with proper indexing

• Multi-column embeddings - Enable vector search on metadata fields

• Full-text search - FTS5 support for metadata fields

Metadata Field Attributes

When defining metadata fields, you can specify several attributes:

Attribute	Default	Description
type	Required	Field type: TEXT, INTEGER, REAL, BOOLEAN, DATE, or JSON
indexed	False	Create database index for faster filtering
required	False	Field must be provided when adding documents
default_value	None	Default value for optional fields
embedding_enabled	False	Generate embeddings for vector search (TEXT/JSON only)
fts_enabled	False	Enable full-text search with FTS5 (TEXT only)

Predefined Schema Templates

Instead of hand-writing a schema, you can start from one of several built-in templates via get_common_metadata_schemas(). Pass the template name directly as the metadata_schema argument when creating a database:

from localvectordb import VectorDB

# Use the built-in "research_papers" schema (title, authors, abstract,
# publication_date, journal, doi, keywords, citation_count)
db = VectorDB("papers", "./storage", metadata_schema="research_papers")

The available templates are files, documents, research_papers, code_repository, and customer_support. You can also inspect or extend a template programmatically:

from localvectordb import get_common_metadata_schemas
from localvectordb.core import MetadataField, MetadataFieldType

schema = dict(get_common_metadata_schemas("documents"))   # a copy to extend
schema["language"] = MetadataField(type=MetadataFieldType.TEXT, indexed=True)
db = VectorDB("docs", "./storage", metadata_schema=schema)

Calling get_common_metadata_schemas() with no argument returns a dict of all templates; an unknown name raises KeyError.

Supported Operators

Comparison Operators

Operator	Description
$eq	Equal to (default for simple values)
$ne	Not equal to
$gt	Greater than
$lt	Less than
$gte	Greater than or equal to
$lte	Less than or equal to
$in	Value in list
$nin	Value not in list

String Operators

Operator	Description
$like	SQL LIKE pattern matching. This uses SQLite’s built-in LIKE, which is case-insensitive for ASCII characters by default.
$ilike	Case-insensitive LIKE matching. Lowercases both the column and the value (LOWER(field) LIKE ...) for reliable case-insensitive matching regardless of SQLite’s LIKE settings.
$contains	Contains substring
$startswith	Starts with substring
$endswith	Ends with substring

Existence Operators

Operator	Description
$exists	Field exists and is not null
$not_exists	Field does not exist or is null

Type Operators

Operator	Description
$type	Check field type: null, string, number, integer, real, boolean, array, object

Logical Operators

Operator	Description
$and	All conditions must be true
$or	At least one condition must be true
$not	Condition must be false

JSON Operators

For metadata fields with JSON type:

Operator	Description
$contains	JSON array contains value
$not_contains	JSON array does not contain value

Basic Usage Examples

Simple Equality

Filter documents with exact field matches:

# Find documents by a specific author
docs = db.filter(where={"author": "John Doe"})

# Find published documents by John Doe from 2023
docs = db.filter(where={
    "author": "John Doe",
    "year": 2023,
    "published": True
})

Range Queries

Filter with numeric or date ranges:

# Find documents between 2020-2024 (inclusive) with a rating > 4.0
docs = db.filter(where={
    "year": {"$gte": 2020, "$lte": 2024},
    "rating": {"$gt": 4.0}
})

# Find documents created in 2024 but updated before December 31st
docs = db.filter(where={
    "created_at": {"$gte": "2024-01-01"},
    "updated_at": {"$lt": "2024-12-31"}
})

String Operations

Search and match text content:

# Find documents with "python" in title, authors starting with "Dr.", and case-insensitive machine learning in description
docs = db.filter(where={
    "title": {"$contains": "python"},
    "author": {"$startswith": "Dr."},
    "description": {"$ilike": "%machine learning%"}
})

# Find documents with company emails and tutorial categories
docs = db.filter(where={
    "email": {"$like": "%@company.com"},
    "category": {"$endswith": "_tutorial"}
})

List Operations

Work with lists and arrays:

# Find documents in tech, science, or education categories, excluding drafts and archived documents
docs = db.filter(where={
    "category": {"$in": ["tech", "science", "education"]},
    "status": {"$nin": ["draft", "archived"]}
})

# Find documents with "python" in tags array but without "deprecated" in keywords
docs = db.filter(where={
    "tags": {"$contains": "python"},  # JSON array contains "python"
    "keywords": {"$not_contains": "deprecated"}
})

Advanced Usage Examples

Logical Operations

Combine multiple conditions with logical operators:

# Find tech documents from 2020+ with rating >= 4.0 (explicit AND)
docs = db.filter(where={
    "$and": [
        {"year": {"$gte": 2020}},
        {"rating": {"$gte": 4.0}},
        {"category": "tech"}
    ]
})

# Find documents by John Doe, Jane Smith, or in the featured category
docs = db.filter(where={
    "$or": [
        {"author": "John Doe"},
        {"author": "Jane Smith"},
        {"category": "featured"}
    ]
})

# Find recent tech documents (by category OR programming tags) that are NOT archived
docs = db.filter(where={
    "$and": [
        {"year": {"$gte": 2020}},
        {"$or": [
            {"category": "tech"},
            {"tags": {"$contains": "programming"}}
        ]},
        {"$not": {"status": "archived"}}
    ]
})

Existence and Type Checking

Validate field presence and types:

# Find documents missing optional fields but having required fields
docs = db.filter(where={
    "optional_field": {"$exists": False},
    "required_field": {"$exists": True}
})

# Find documents with specific data types (numeric ratings, array tags, object metadata, string descriptions)
docs = db.filter(where={
    "rating": {"$type": "number"},
    "tags": {"$type": "array"},
    "metadata": {"$type": "object"},
    "description": {"$type": "string"}
})

Ordering and Pagination

Sort and paginate filtered results:

# Find tech documents, order by newest first, get page 3 (items 21-30)
docs = db.filter(
    where={"category": "tech"},
    order_by="created_at DESC",
    limit=10,
    offset=20
)

# Find recent documents with highest ratings first, limit to 50 results
docs = db.filter(
    where={"year": {"$gte": 2020}},
    order_by="rating DESC",
    limit=50
)

API Usage Examples

Local Database (LocalVectorDB)

Direct usage with the local database:

from localvectordb import LocalVectorDB

# Initialize database
db = LocalVectorDB("my_database")

# Find recent documents by John Doe or Jane Smith from 2020 onwards
docs = db.filter(where={
    "author": {"$in": ["John Doe", "Jane Smith"]},
    "year": {"$gte": 2020}
})

# Find recent research documents: approved peer-reviewed papers OR highly cited papers, newest first
docs = db.filter(
    where={
        "$and": [
            {"category": "research"},
            {"$or": [
                {"peer_reviewed": True},
                {"citation_count": {"$gte": 100}}
            ]}
        ]
    },
    order_by="publication_date DESC",
    limit=25
)

Remote Database (RemoteVectorDB)

Client usage for remote databases:

from localvectordb.client import RemoteVectorDB

# Connect to remote database
db = RemoteVectorDB(
    "my_database",
    "http://localhost:8000",
    api_key="your_api_key"
)

# Find high-quality tech documents in English or Spanish
docs = db.filter(where={
    "$and": [
        {"category": "tech"},
        {"rating": {"$gte": 4.0}},
        {"language": {"$in": ["en", "es"]}}
    ]
})

REST API Usage

HTTP API examples using curl:

Simple Filtering

Find documents by John Doe from 2020 onwards:

curl -X POST "http://localhost:8000/api/v1/databases/my_database/filter" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your_api_key" \
  -d '{
    "where": {
      "author": "John Doe",
      "year": {"$gte": 2020}
    }
  }'

Complex Filtering with Pagination

Find high-quality tech/science documents that are featured OR trending, newest first:

curl -X POST "http://localhost:8000/api/v1/databases/my_database/filter" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your_api_key" \
  -d '{
    "where": {
      "$and": [
        {"category": {"$in": ["tech", "science"]}},
        {"rating": {"$gte": 4.0}},
        {"$or": [
          {"featured": true},
          {"trending": true}
        ]}
      ]
    },
    "order_by": "created_at DESC",
    "limit": 10,
    "offset": 0
  }'

String Search Operations

Find machine learning articles by doctors with AI in the description:

curl -X POST "http://localhost:8000/api/v1/databases/my_database/filter" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your_api_key" \
  -d '{
    "where": {
      "title": {"$contains": "machine learning"},
      "author": {"$startswith": "Dr."},
      "description": {"$ilike": "%artificial intelligence%"}
    },
    "limit": 20
  }'

JSON Array Operations

Find Python documents without deprecated content, with specific skill requirements:

curl -X POST "http://localhost:8000/api/v1/databases/my_database/filter" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your_api_key" \
  -d '{
    "where": {
      "tags": {"$contains": "python"},
      "categories": {"$not_contains": "deprecated"},
      "skills_required": {"$in": [["python"], ["javascript"], ["python", "sql"]]}
    }
  }'

Search Integration

Enhanced filtering works seamlessly with search operations:

Vector Search with Filtering

# Search for machine learning tutorials: recent, tech/education category, intermediate difficulty or below
results = db.query(
    "machine learning tutorial",
    search_type="vector",
    filters={
        "year": {"$gte": 2020},
        "category": {"$in": ["tech", "education"]},
        "difficulty": {"$lte": "intermediate"}
    }
)

Hybrid Search with Complex Filtering

# Search for Python programming content: English language, beginner-friendly OR tutorial content, high quality
results = db.query(
    "python programming",
    search_type="hybrid",
    filters={
        "$and": [
            {"language": "en"},
            {"$or": [
                {"level": "beginner"},
                {"tags": {"$contains": "tutorial"}}
            ]},
            {"rating": {"$gte": 4.0}}
        ]
    },
    vector_weight=0.5
)

Search API with Filtering

Search for recent NLP content in AI category OR with NLP tags:

curl -X POST "http://localhost:8000/api/v1/databases/my_database/query" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your_api_key" \
  -d '{
    "query": "natural language processing",
    "search_type": "hybrid",
    "filters": {
      "$and": [
        {"year": {"$gte": 2020}},
        {"$or": [
          {"category": "ai"},
          {"tags": {"$contains": "nlp"}}
        ]}
      ]
    },
    "k": 10
  }'

Performance and Best Practices

Indexing Strategy

Create indexes on frequently filtered fields:

from localvectordb.core import MetadataField, MetadataFieldType

# Define schema with indexed fields and optional embeddings
metadata_schema = {
    "author": MetadataField(type=MetadataFieldType.TEXT, indexed=True),
    "title": MetadataField(type=MetadataFieldType.TEXT, indexed=True,
                         embedding_enabled=True,  # Enable vector search on title
                         fts_enabled=True),        # Enable full-text search
    "abstract": MetadataField(type=MetadataFieldType.TEXT,
                            embedding_enabled=True),  # Enable embeddings without indexing
    "year": MetadataField(type=MetadataFieldType.INTEGER, indexed=True),
    "category": MetadataField(type=MetadataFieldType.TEXT, indexed=True),
    "rating": MetadataField(type=MetadataFieldType.REAL, indexed=True),
    "tags": MetadataField(type=MetadataFieldType.JSON, indexed=False)  # JSON operations are slower
}

Query Optimization Tips

1. Use indexed fields for frequently filtered columns

2. Combine simple conditions before using logical operators

3. Place selective filters first in $and operations

4. Use appropriate operators for data types

5. Limit result sets with limit parameter

# Good: Place selective filter first, then broader filter
# Find documents by a specific author (selective), then filter by broader categories
docs = db.filter(where={
    "$and": [
        {"author": "Specific Author"},      # Very selective
        {"category": {"$in": ["tech", "science"]}}  # Less selective
    ]
})

# Good: Use appropriate operators for data types
# Find high-rated AI documents with boolean exact match
docs = db.filter(where={
    "rating": {"$gte": 4.0},        # Numeric comparison
    "title": {"$contains": "AI"},   # String search
    "published": True               # Boolean exact match
})

Performance Considerations

Note

JSON Operations: $contains and $not_contains on JSON fields may be slower on large datasets. Consider restructuring data if these operations are frequent.

Tip

Complex Queries: Test complex logical operations with small datasets first to understand performance characteristics.

Error Handling

The filtering system provides clear error messages for common issues:

Invalid Field Names

try:
    docs = db.filter(where={"invalid_field": "value"})
except DatabaseError as e:
    print(f"Filter error: {e}")
    # Error: Field 'invalid_field' not found in metadata schema

Unsupported Operators

try:
    docs = db.filter(where={"author": {"$invalid": "value"}})
except DatabaseError as e:
    print(f"Filter error: {e}")
    # Error: Unsupported operator: $invalid

Operator/Type Mismatches

Using a string operator such as $contains against a non-text field does not raise; it falls back to a substring LIKE match on the value’s text form, which is rarely what you want. Prefer the numeric comparison operators for numeric fields:

# Silently does a LIKE '%2020%' match — usually not intended:
docs = db.filter(where={"year": {"$contains": "2020"}})

# Use a numeric comparison instead:
docs = db.filter(where={"year": 2020})
docs = db.filter(where={"year": {"$gte": 2020, "$lt": 2025}})

Common Error Messages

Error Type	Description
Field 'field_name' not found in metadata schema	Field doesn’t exist in the database schema
Unsupported operator: $operator	Operator not recognized or not supported
Invalid field name: field_name	Field name contains invalid characters
Operator $op requires a list/array value	Wrong value type for operator (e.g., $in with non-list)
JSON operations only supported on JSON fields	Attempting JSON operations on non-JSON field

Complete Examples

Research Paper Database

from localvectordb import LocalVectorDB
from localvectordb.core import MetadataField, MetadataFieldType

# Create database with research paper schema
metadata_schema = {
    "title": MetadataField(type=MetadataFieldType.TEXT, indexed=True),
    "authors": MetadataField(type=MetadataFieldType.JSON),
    "year": MetadataField(type=MetadataFieldType.INTEGER, indexed=True),
    "journal": MetadataField(type=MetadataFieldType.TEXT, indexed=True),
    "citations": MetadataField(type=MetadataFieldType.INTEGER, indexed=True),
    "keywords": MetadataField(type=MetadataFieldType.JSON),
    "peer_reviewed": MetadataField(type=MetadataFieldType.BOOLEAN, indexed=True)
}

db = LocalVectorDB("research_papers", metadata_schema=metadata_schema)

# Find highly cited machine learning papers from top journals (100+ citations, recent, peer-reviewed)
papers = db.filter(where={
    "$and": [
        {"keywords": {"$contains": "machine learning"}},
        {"citations": {"$gte": 100}},
        {"journal": {"$in": ["Nature", "Science", "ICML", "NeurIPS"]}},
        {"year": {"$gte": 2020}},
        {"peer_reviewed": True}
    ]
}, order_by="citations DESC", limit=20)

E-commerce Product Database

# Find affordable electronics from premium brands OR with wireless features, in stock, not discontinued
product_filters = {
    "$and": [
        {"category": {"$in": ["electronics", "computers"]}},
        {"price": {"$gte": 100, "$lte": 1000}},
        {"rating": {"$gte": 4.0}},
        {"in_stock": True},
        {"$or": [
            {"brand": {"$in": ["Apple", "Samsung", "Sony"]}},
            {"features": {"$contains": "wireless"}}
        ]},
        {"$not": {"discontinued": True}}
    ]
}

products = db.filter(
    where=product_filters,
    order_by="rating DESC",
    limit=50
)

Document Management System

# Find recent approved documents by key authors/departments OR urgent items, excluding confidential drafts
documents = db.filter(where={
    "$and": [
        {"status": "approved"},
        {"created_at": {"$gte": "2024-01-01"}},
        {"$or": [
            {"author": {"$in": ["John Doe", "Jane Smith"]}},
            {"department": {"$in": ["engineering", "research"]}},
            {"tags": {"$contains": "urgent"}}
        ]},
        {"document_type": {"$nin": ["draft", "template"]}},
        {"confidential": {"$ne": True}}
    ]
}, order_by="created_at DESC")