Filter by Non-Existing Field

To run a dynamic query over a collection and find which documents are missing a specified field,
use the Not and WhereExists extension methods, accessible from the DocumentQuery API, as shown below.

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

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

Documents with missing fields can be searched by querying a static index.

The index definition must contain the following document fields indexed:

• A document field that exists in all documents of the queried collection, e.g. the Id field.
  Indexing this field will ensure that all the documents of this collection are indexed.
• A document field that is suspected to be missing from some documents of the queried collection.

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
            };
    }
}

// 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`.

• Documents can be searched by missing fields using 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 all documents of the queried collection, e.g. the Id field.
  Then search for a field that may be missing from some documents of the queried collection.

  

  Query for documents that are missing the specified field

  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 to run the query.
  5. Index used
     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.
     (the "Freight" Field was removed from these Northwind documents for this example.)