Cosmos DB SQL query to search for a string across all fields in a document.

Question

I’m using Azure Cosmos DB (SQL API) and want to find documents where a specific string appears anywhere in the document, regardless of field name.

What is the recommended query pattern for this, and what are the performance implications?

Answer 1

Cosmos DB does not support a built-in "search all fields" operator in the SQL query language. A query must target specific properties or a single aggregated text field.

Recommended patterns:

1. Use full-text search on a specific property
   • Create a full-text index on the property that contains the text to search, for example /text:

          {
            "indexingMode": "consistent",
            "automatic": true,
            "includedPaths": [
              { "path": "/*" }
            ],
            "excludedPaths": [
              { "path": "/\"_etag\"/?" }
            ],
            "fullTextIndexes": [
              { "path": "/text" }
            ]
          }
     

   • Query using full-text functions such as FULLTEXTCONTAINS, FULLTEXTCONTAINSALL, or FULLTEXTCONTAINSANY:

          SELECT TOP 10 c.text
          FROM c
          WHERE FULLTEXTCONTAINSANY(c.text, "searchTerm1", "searchTerm2")
     

   • This requires that the document schema includes a property (for example text) that aggregates the content to be searched (for example, concatenation of relevant fields) and that the container is enrolled in the full-text search feature. This is the recommended pattern when flexible text search is needed.
2. Use LIKE on specific properties
   • If full-text search is not used, define range indexes on the properties to be searched and use LIKE:

          SELECT VALUE p.name
          FROM products p
          WHERE p.name LIKE "%driver%"
     

   • This pattern must list each property explicitly; it cannot search all fields generically.

Performance implications:

• Full-text search
  • Requires a full-text index on the searched paths. Queries using FULLTEXTCONTAINS* functions benefit from this index and are efficient for text search scenarios.
  • Indexing cost increases with the number and size of indexed text fields; write latency and RU consumption for writes can increase because more index data is maintained.
  • Query RU charge depends on the number of documents scanned and complexity of the search expression, but is generally much lower than scanning all documents without an index.
• LIKE on multiple fields
  • Requires range indexes on each property used with LIKE for efficient execution.
  • If many properties are searched with LIKE, queries can become expensive in RUs and complex to maintain, and still do not provide ranking or linguistic search features.

To approximate "search across all fields", the practical approach is to:

• Materialize a single searchable text field (for example c.text) that concatenates all relevant properties in the document.
• Create a full-text index on that field.
• Use FULLTEXTCONTAINS* functions in queries against that field.

---

References:

• What is the query language in Cosmos DB (in Azure and Fabric)?
• LIKE - Query language in Cosmos DB (in Azure and Fabric)
• Full-text search in Azure Cosmos DB for NoSQL
• FULLTEXTCONTAINSALL - Query language in Cosmos DB (in Azure and Fabric)
• FULLTEXTCONTAINSANY - Query language in Cosmos DB (in Azure and Fabric)
• Full text indexing and search in Cosmos DB (in Azure and Fabric)
• Full text indexing and search in Cosmos DB in Fabric