Skip to main content

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".

Excluding Records with NOT​

Put NOT in front of a condition to exclude the records that match it. Write NOT in uppercase. You can use it after AND or OR, at the start of a query, or between two conditions.

Query: status:open AND NOT priority:low

Expected Result: Returns records where the "status" field contains "open", except those whose "priority" field contains "low".

Query: NOT priority:low AND status:open

Expected Result: The same records. NOT can come first.

Query: status:open NOT priority:low

Expected Result: The same records. NOT between two conditions means AND NOT.

Query: status:open AND !priority:low

Expected Result: The same records. An exclamation mark directly in front of a condition (with no space) is the same as NOT.

Query: status:open AND NOT (priority:low OR priority:medium)

Expected Result: Returns open records, except those whose priority is low or medium. NOT in front of parentheses excludes every record that matches anything inside them.

Query: status:open AND NOT blue

Expected Result: A plain word can be excluded too. Returns open records, except those that contain the word "blue" in their name or in any cell.

Query: product:laptop AND NOT meta.createdBy:john

Expected Result: NOT also works with metadata fields. Returns laptop records that were not created by john.

Things to know:

  • A record that has no such field is not excluded. NOT priority:low excludes the records whose "priority" contains "low". A record that has no "priority" value at all is still returned. This differs from priority:!=low, which only returns records that have a "priority" value that is not "low".
  • NOT needs something to exclude. A query that ends in NOT, such as status:open AND NOT, is rejected with an error.
  • Use NOT, not a minus sign, in front of a field condition. status:open AND -priority:low is rejected. A minus sign works only in front of a plain word, as in status:open AND -blue.
  • Use parentheses when you mix AND, OR and NOT. Parentheses make the order explicit and are the only way to be sure how a longer query is grouped: status:open AND (NOT priority:low OR category:urgent).

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.

Logical Operators Must Be Uppercase​

Write the logical operators AND, OR and NOT in uppercase.

Lowercase Words Are Not Operators​

Query: status:pending and priority:high

Expected Result: Lowercase and, or and not are not operators. In a field search they are treated as ordinary words to look for, so this query does not mean "pending AND high". It also matches records that contain the word "and" anywhere in their name or cells, and so returns more records than you intended.

Query: status:pending AND priority:high

Expected Result: Written with an uppercase operator, both conditions must be true.

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:

  1. Field Recognition: AnyDB identifies which parts of your query are content field searches (cells from your template) and which are metadata searches (meta. prefix)
  2. Value Matching: For text fields, AnyDB finds records containing your search text. For numeric fields, it applies the appropriate comparison
  3. Metadata Filtering: Any metadata conditions are applied to narrow down results further
  4. 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"