Search in AnyDB
Overview
AnyDB allows you to search through your records using a powerful query syntax. Each record contains:
- Content fields: The cells you defined in your template (e.g., product name, price, quantity)
- Metadata fields: System-managed information about the record itself (name, created timestamp, created by user, updated timestamp, updated by user)
You can search using either content fields, metadata fields, or a combination of both.
Search Query Syntax
AnyDB provides a powerful search query syntax that allows you to search and filter records using field-based queries with logical operators.
Simple Value Queries
This is the most common search usecase. Search both content fields and meta fields by typing a simple word or a phrase.
Query: laptop
Expected Result: Returns all records where the term "laptop" occurs either in their cell or metadata fields.
Content Field Queries
Content fields are the cells you defined in your template. You can search across any of these fields using the field:value pattern.
Simple Field:Value Pattern
Query: product:laptop
Expected Result: Returns all records where the "product" field contains "laptop".
Multiple Conditions with AND
Query: product:laptop AND price:>500
Expected Result: Returns records where both conditions are true. The "product" field must contain "laptop" AND the "price" field must be greater than 500.
Multiple Conditions with OR
Query: status:pending OR status:processing
Expected Result: Returns records where at least one condition is true. Find records where status is either "pending" OR "processing".
Numeric Comparison Operators
When your records includes numeric fields (price, quantity, rating, etc.), you can use comparison operators to filter by numeric ranges.
Greater Than (>)
Query: price:>1000
Expected Result: Returns all records where the "price" field has a numeric value greater than 1000.
Greater Than or Equal (>=)
Query: rating:>=4.0
Expected Result: Returns all records where the "rating" field has a numeric value greater than or equal to 4.0.
Less Than (<)
Query: inventory:<50
Expected Result: Returns all records where the "inventory" field has a numeric value less than 50.
Less Than or Equal (<=)
Query: discount:<=0.30
Expected Result: Returns all records where the "discount" field has a numeric value less than or equal to 0.30.
Not Equal (!=)
Query: status:!=archived
Expected Result: Returns all records where the "status" field does not equal "archived".
Numeric Operators with Decimal Values
Query: price:>19.99 AND weight:<=5.5
Expected Result: Numeric operators support decimal values, useful for currency (prices), ratings, percentages, or other decimal measurements in your records.
Numeric Operators with Negative Values
Query: balance:<-100 AND debt:>-1000
Expected Result: Numeric operators correctly handle negative values, useful for financial records, temperature data, or other fields that can contain negative numbers.
Complex Nested Expressions
You can combine multiple conditions with parentheses to create more complex search logic.
Simple Nested Expression
Query: (category:Electronics AND brand:Samsung) OR (category:Appliances AND brand:LG)
Expected Result: Returns records matching either criteria: (Electronics from Samsung) OR (Appliances from LG). Parentheses control the order of operations.
Deeply Nested Expressions
Query: (((category:Books AND author:Stephen King) OR author:George Martin) AND rating:>3.5)
Expected Result: AnyDB supports arbitrary nesting levels, allowing complex combinations of your content fields.
Nested Parentheses with Numeric Operators
Query: ((year:>2020 AND year:<2024) OR (rating:>=4.5 AND status:published))
Expected Result: Returns records where either: (year between 2020 and 2024) OR (rating at least 4.5 and status is published). This shows how to combine numeric and text operators in nested expressions.
Multiple Operators on Same Field
Query: (price:>=100 AND price:<=500)
Expected Result: Returns records where price is between 100 and 500 (inclusive). Use this pattern to create price ranges or budget filters.
Complex Numeric Range
Query: (price:>=10.00 AND price:<=99.99) OR (discount:>0.1 AND discount:<0.5)
Expected Result: Returns records where: (price between $10 and $99.99) OR (discount between 10% and 50%). Useful for promotions or bulk product filtering.
Mixed Numeric and Text Operators
Query: product:laptop AND price:>500 OR category:Computers
Expected Result: Text and numeric operators can be freely mixed. This returns records matching the combined conditions.
Query: brand:Apple AND price:>800
Expected Result: Returns records where the brand is "Apple" AND the price is greater than $800.
Quoted Field Names and Values
Use quotes when field names contain spaces or special characters, or when you need exact phrase matching in values.
Field Name with Spaces
Query: "Product Name":"MacBook Pro" AND (year:>2020 OR category:Computers)
Expected Result: When a field name contains spaces, wrap it in double quotes. Quoted values perform exact phrase matching instead of wildcard matching.
Field Name with Special Characters
Query: "Item #":"ABC-123"
Expected Result: Fields with special characters (like #, -, @) must be quoted. The quoted value ensures exact phrase matching.
Quoted Text Values
Query: "Author Name":"Stephen King" AND category:Horror
Expected Result: Quoted values perform exact phrase matching, useful for matching exact names, titles, or longer text phrases.
Case-Insensitive Logical Operators
Logical operators (AND, OR) can be written in either uppercase or lowercase.
Lowercase Operators
Query: status:pending and priority:high or category:urgent
Expected Result: Lowercase operators (and, or) are interpreted correctly. Use whichever style is most comfortable for you.
Metadata Field Queries
Every record has automatic metadata managed by AnyDB:
- name: The name or title of the record
- created: Timestamp when the record was created
- createdBy: User who created the record
- updated: Timestamp when the record was last updated
- updatedBy: User who last updated the record
You can search using these metadata fields by prefixing them with meta.. Meta queries are always applied as filters to the results from your content searches.
This means that meta searches will always be an AND to content searches.
Simple Metadata Query
Query: meta.createdBy:john.doe
Find all records created by the user "john.doe".
Metadata Query with Text Match
Query: meta.name:Project
Searches for all records whose name contains the text "Project"
Combined Content and Metadata Search
Query: product:laptop AND meta.createdBy:sales@company.com
Find all records containing "laptop" for the product field that were created by "sales@company.com". This combines searching your content fields with metadata filtering.
Multiple Metadata Conditions with AND
Query: meta.createdBy:alice AND meta.name:Report
Find all records created by "alice" AND whose name contains "Report".
Complex Content and Metadata Queries
Query: status:completed AND meta.name:Q4 OR department:sales
Find all records containing "completed" in the status field and "sales" in the department field whose name contains "Q4"
Query: meta.createdBy:john AND meta.name:Document OR category:Report
Find all records containing "Report" in the category field that are created by user john with name containing "Document"
Query: (category:Books AND year:>2020) OR (meta.createdBy:admin AND meta.name:Catalog)
Expected Result: Returns records that are either Books published after 2020 OR records named "Catalog" that were created by an admin user. This demonstrates how complex nested expressions can combine both content field searches with metadata filtering.
Advanced Search Patterns
Field Names with Spaces or Special Characters
Query: "Order ID":"ORD-2024-001" AND "Total Price":>500
Expected Result: Field names can contain spaces, hyphens, and special characters when quoted, giving you flexibility in how you name your cells. Value fields with spaces, hyphens, and special characters should be quoted as well (as the indexer tokenizes them as different words)
Range Queries for Budget and Price Filtering
Query: (price:>=100.00 AND price:<=500.00) OR (discount:>0.10 AND discount:<0.50)
Expected Result: Returns records with price between $100-500 OR discount between 10%-50%. Useful for inventory or product filtering.
Whitespace Tolerance
Query: status:active AND priority:high OR category:urgent
Expected Result: Extra whitespace around operators is handled gracefully. You can format queries however is most readable for you.
How Search Works in AnyDB
When you submit a search query, AnyDB processes it in these steps:
- Field Recognition: AnyDB identifies which parts of your query are content field searches (cells from your template) and which are metadata searches (
meta.prefix) - Value Matching: For text fields, AnyDB finds records containing your search text. For numeric fields, it applies the appropriate comparison
- Metadata Filtering: Any metadata conditions are applied to narrow down results further
- Results: You get back all records matching your search criteria
This two-part approach (content + metadata) ensures fast and efficient searching across your records.
Best Practices for Searching Records
- Use field names exactly as defined: Match the exact field names from your template. If you're unsure, check your template definition
- Quote field names with spaces: When a field name contains spaces, wrap it in double quotes:
"Full Name":"John Doe" - Quote field names with special characters: Use quotes for fields containing
#,-,@, etc.:"Order #":ORD-2024 - Use range queries for numeric fields: Create price or quantity ranges with multiple conditions:
(price:>=100 AND price:<=500) - Filter by creator: Search for records created by specific users:
meta.createdBy:username - Combine content and metadata searches: Search your data AND filter by who created it:
status:completed AND meta.createdBy:john - Use parentheses for complex logic: Make your queries easier to read and understand:
(category:Books AND author:Smith) OR (category:Fiction) - Use exact phrases when needed: Use quotes to match exact names or titles:
"Project Name":"Q4 Budget Review"