Patching: How to Perform Set Based Operations on Documents
Sometimes we need to update a large amount of documents answering certain criteria. A simple SQL query doing that will look like this:
UPDATE Users SET IsActive = 0 WHERE LastLogin < '2010-01-01'
This is usually not the case for NoSQL databases where set based operations are not supported. RavenDB does support them by passing it a query and an operation definition. It will run the query and perform that operation on its results.
The same queries and indexes that are used for data retrieval are used for the set based operations. The syntax defining which documents to work on is exactly the same as you'd specified for those documents to be pulled from the store.
In this page:
Syntax overview
Examples
Additional notes
Syntax overview
Sending a Patch Request
Operation Send(PatchByQueryOperation operation);
Parameters | ||
---|---|---|
operation | PatchByQueryOperation |
PatchByQueryOperation object, describing the query and the patch that will be performed |
Return Value | |
---|---|
Operation |
Object that allows waiting for operation to complete. It also may return information about a performed patch: see examples below. |
PatchByQueryOperation
public PatchByQueryOperation(string queryToUpdate)
public PatchByQueryOperation(IndexQuery queryToUpdate, QueryOperationOptions options = null)
Parameters | ||
---|---|---|
queryToUpdate | string or IndexQuery |
RQL query defining the update operation. The RQL query starts as any other RQL query with "from" and "update" statements. Later, it continues with an "update" clause in which you describe the Javascript patch code |
options | QueryOperationOptions |
Options defining how the operation will be performed and various constraints on how it is performed |
Examples
Update whole collection
// increase by 10 Freight field in all orders
var operation = store
.Operations
.Send(new PatchByQueryOperation(@"from Orders as o
update
{
o.Freight +=10;
}"));
// Wait for the operation to be complete on the server side.
// Not waiting for completion will not harm the patch process and it will continue running to completion.
operation.WaitForCompletion();
Update by dynamic query
// set discount to all orders that was processed by a specific employee
var operation = store
.Operations
.Send(new PatchByQueryOperation(@"from Orders as o
where o.Employee = 'employees/4-A'
update
{
o.Lines.forEach(line=> line.Discount = 0.3);
}"));
operation.WaitForCompletion();
Update by static index query result
// switch all products with supplier 'suppliers/12-A' with 'suppliers/13-A'
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from index 'Product/Search' as p
where p.Supplier = 'suppliers/12-A'
update
{
p.Supplier = 'suppliers/13-A'
}"
}));
operation.WaitForCompletion();
Updating a collection name
// delete the document before recreating it with a different collection name
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from Orders as c
update
{
del(id(c));
this[""@metadata""][""@collection""] = ""New_Orders"";
put(id(c), this);
}"
}));
operation.WaitForCompletion();
Updating by document ID
// perform a patch by document ID
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from @all_docs as d
where id() in ('orders/1-A', 'companies/1-A')
update
{
d.Updated = true;
}"
}));
operation.WaitForCompletion();
Updating by document ID using parameters
// perform a patch by document ID
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
QueryParameters = new Parameters
{
{"ids", new[] {"orders/1-A", "companies/1-A"}}
},
Query = @"from @all_docs as d
where id() in ($ids)
update
{
d.Updated = true;
}"
}));
operation.WaitForCompletion();
Updating all documents
// perform a patch on all documents using @all_docs keyword
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from @all_docs
update
{
this.Updated = true;
}"
}));
operation.WaitForCompletion();
Patch on stale results
// patch on stale results
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from Orders as o
where o.Company = 'companies/12-A'
update
{
o.Company = 'companies/13-A'
}"
},
new QueryOperationOptions
{
AllowStale = true
}));
operation.WaitForCompletion();
Report progress on patch
// report progress during patch processing
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from Orders as o
where o.Company = 'companies/12-A'
update
{
o.Company = 'companies/13-A'
}"
},
new QueryOperationOptions
{
AllowStale = true
}));
operation.OnProgressChanged = x =>
{
DeterminateProgress progress = (DeterminateProgress)x;
Console.WriteLine($"Progress: Processed: {progress.Processed}; Total: {progress.Total}");
};
operation.WaitForCompletion();
Process patch results details
// perform patch and create summary of processing statuses
var operation = store
.Operations
.Send(new PatchByQueryOperation(new IndexQuery
{
Query = @"from Orders as o
where o.Company = 'companies/12-A'
update
{
o.Company = 'companies/13-A'
}"
},
new QueryOperationOptions
{
RetrieveDetails = true
}));
var result = operation.WaitForCompletion<BulkOperationResult>();
var formattedResults =
result.Details
.Select(x => (BulkOperationResult.PatchDetails)x)
.GroupBy(x => x.Status)
.Select(x => $"{x.Key}: {x.Count()}").ToList();
formattedResults.ForEach(Console.WriteLine);
Additional notes
Safe By Default
By default, set based operations will not work on indexes that are stale. The operations will only succeed if the specified index is not stale. This is to make sure you only delete what you intended to delete.
For indexes that are updated all the time, you can set the AllowStale field of QueryOperationOptions to true if you want to patch on stale results.
Patching and Concurrency
The patching of documents matching a specified query is run in batches of size 1024. RavenDB doesn't do concurrency checks during the operation so it can happen than a document has been updated or deleted meanwhile.
Patching and Transaction
The patching of documents matching a specified query is run in batches of size 1024.
Each batch is handled in a separate write transaction.