Filter by Non-Existing Field

• There are situations where new fields are added to some documents in a collection over time.

• To find the documents that are missing the newly added fields you can either:

  • Query the collection (dynamic query)
  • Query a static index
  • Query by RQL in Studio

---

Query the collection (dynamic query)

• You can make a dynamic query on a collection to find which documents are missing the specified field.

• Use extension methods Not & WhereExists that are accessible from DocumentQuery.

• This will either create a new auto-index or add the queried field to an existing auto-index.
  Learn more about the dynamic query flow here.

Example

• DocumentQuery
• DocumentQuery_async
• RQL

List<Order> ordersWithoutFreightField = session
  .Advanced
   // Define a DocumentQuery on 'Orders' collection
  .DocumentQuery<Order>()
   // Search for documents that do Not contain field 'Freight'
  .Not.WhereExists("Freight")
   // Execute the query
  .ToList();

// Results will be only the documents that do Not contain the 'Freight' field in 'Orders' collection

List<Order> ordersWithoutFreightField = await asyncSession
  .Advanced
   // Define a DocumentQuery on 'Orders' collection
  .AsyncDocumentQuery<Order>()
   // Search for documents that do Not contain field 'Freight'
  .Not.WhereExists("Freight")
   // Execute the query
  .ToListAsync();

// Results will be only the documents that do Not contain the 'Freight' field in 'Orders' collection

from "Orders"
where true and not exists("Freight")
// `not` cannot be used immediately after `where`, thus we use `where true`.

Query a static index

• You can search for documents with missing fields by querying a static index.

• The index definition must have the following document-fields indexed:

  1. The field that is suspected to be missing in some documents.

  2. A document-field that exists in all documents in the collection,
     (i.e. the Id field, or any other field that is common to all).
     Indexing such a field is mandatory so that all documents in the collection will be indexed.

Example

// Define a static index on the 'Orders' collection
// ================================================

public class Orders_ByFreight : AbstractIndexCreationTask<Order, Orders_ByFreight.IndexEntry>
{
  public class IndexEntry
  {
      // Define the index-fields
      public decimal Freight { get; set; }
      public string Id { get; set; }
  }
  
  public Orders_ByFreight()
  {
      // Define the index Map function
      Map = orders => from doc in orders
          select new IndexEntry
          {
              // Index a field that might be missing in SOME documents
              Freight = doc.Freight,
              // Index a field that exists in ALL documents in the collection
              Id = doc.Id
          };
  }
}

• DocumentQuery
• DocumentQuery_async
• RQL

// Query the index
// ===============

List<Order> ordersWithoutFreightField = session
  .Advanced
   // Define a DocumentQuery on the index
  .DocumentQuery<Order, Orders_ByFreight>()
   // Verify the index is not stale (optional)
  .WaitForNonStaleResults()
   // Search for documents that do Not contain field 'Freight'
  .Not.WhereExists(x => x.Freight)
   // Execute the query
  .ToList();

// Results will be only the documents that do Not contain the 'Freight' field in 'Orders' collection

// Query the index
// ===============

List<Order> ordersWithoutFreightField = await asyncSession
  .Advanced
   // Define a DocumentQuery on the index
  .AsyncDocumentQuery<Order, Orders_ByFreight>()
   // Verify the index is not stale (optional)
  .WaitForNonStaleResults()
   // Search for documents that do Not contain field 'Freight'
  .Not.WhereExists(x => x.Freight)
   // Execute the query
  .ToListAsync();

// Results will be only the documents that do Not contain the 'Freight' field in 'Orders' collection

from index "Orders/ByFreight"
where true and not exists("Freight")
// `not` cannot come immediately after `where`, thus we use `where true`.

Query by RQL in Studio

• You can query for documents with missing fields in the Studio's Query view.

• Use an RQL expression such as:

from "Orders"    
where exists("Company") and not exists("Freight")

• In the where clause,
  first search for a field that exists in every document in the collection,
  and then search for the field that may not exist in some of document.

1. Indexes
   Click to see the Indexes menu.
2. Query
   Select to open the Query view.
3. Query editor
   Write the RQL query.
4. Run Query
   Click or press ctrl+enter to run the query.
5. Index used
   This is the name of the auto-index created to serve this query.
   You can click it to see the available Studio options for this index.
6. Results
   This is the list of documents that do not contain the specified 'Freight' field.
   (Field "Freight" was explicitly removed from these Northwind documents for this example.)