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)
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 query 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
const ordersWithoutFreightField = await session
// Define a query on 'orders' collection
.query({ collection: "orders" })
// Search for documents that do Not contain field 'freight'
.not()
.whereExists("freight")
// Execute the query
.all();
// 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
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
// ================================================
class Orders_ByFreight extends AbstractJavaScriptIndexCreationTask<Order> {
constructor() {
super();
// Define the index-fields
this.map("orders", o => ({
// Index a field that might be missing in SOME documents
freight: o.firstName,
// Index a field that exists in ALL documents in the collection
id: o.lastName
}));
}
}
// Query the index
// ===============
const employees = await session
// Define a query on the index
.query({ indexName: "Orders/ByFreight" })
// Search for documents that do Not contain field 'freight'
.not()
.whereExists("freight")
// Execute the query
.all();
// 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`.
Use Studio to Run an RQL Query
-
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
- Indexes
Click to see the Indexes menu. - Query
Select to open the Query view. - Query editor
Write the RQL query. - Run Query
Click to run the query. - 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. - 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.)
- Indexes