- Loading Your Data
- Collections
- Updating Data in Collections
Updating Data in Collections
This page covers how to add, delete, and patch documents in collections.
#Overview
When a collection is created from a managed integration, Rockset will automatically sync your collection to remain up-to-date with its data source, usually within a matter of seconds after the data source is updated (you can read more about individual source behavior in the Data Sources section).
If you choose not to create your collection using a managed integration, or wish to make manual changes to data in your collection after Rockset has synced it with your external data source, you can do so using the Rockset API or the SQL INSERT INTO statement.
#Using the Rockset API
You can add, delete, and patch documents using their respective endpoints in the Rockset API.
#Adding Documents
To add new documents to your collections, you can format your new document data in JSON and make a request to the Add Documents endpoint.
#Deleting Documents
To delete existing documents from your collections, simply specify the _id
fields of the documents
you wish to remove and make a request to the Delete Documents
endpoint.
#Patching Documents
To update existing documents in a collection using the Rockset API, you can make requests to the Patch Documents endpoint. For each existing document you wish to update, you will need to specify the following two parameters:
_id
holding the_id
field (primary key) of the document which is being patchedpatch
holding a list of patch operations to be applied to that document, following the JSON Patch standard.
Each patch operation is a dictionary with a key opstring
indicating the patch operation, and
additional keys pathstring
, valueobject
, and fromstring
which are used as required arguments
for this patch operation. The required arguments differ from one operation type to another. The JSON
Patch standard defines several types of patch operations, their arguments, and their behavior. Refer
to the JSON Patch documentation for more details.
If a patch operation’s argument is a field path, then it is specified using the JSON Pointer
standard defined by the IETF. In essence, field paths are
represented as a string of tokens separated by /
characters. These tokens either specify keys in
objects or indexes into arrays, and arrays are 0-based.
For example, in this document:
{
"biscuits": [{ "name": "Digestive" }, { "name": "Choco Leibniz" }]
}
The path "/biscuits"
would point to the biscuits
array, while the path "/biscuits/1/name"
would point to "Choco Leibniz"
.
There are six supported JSON patch operations:
add
which adds a value (specified by the value parameter) to an object or inserts it into an array (specified by the path parameter). In the case of an array, the value is inserted before the given index. The-
character can be used instead of an index to insert at the end of an array. The parameterspathstring
andvalueobject
are required for this operation.remove
which removes the first instance of an object or element of an array (specified by the path parameter). The parameterpathstring
is required for this operation.replace
which replaces the first instance of an object or element of an array (specified by the path parameter) with a value (specified by the value parameter). This operation is equivalent to anadd
operation immediately followed aremove
operation. The parameterspathstring
andvalueobject
are required for this operation.copy
which copies a value from one location (specified by the from parameter) to another location (specified by the path parameter) within the JSON document. The parameterspathstring
andfromstring
are required for this operation.move
which moves a value from one location (specified by the from parameter) to another location (specified by the path parameter) within the JSON document. The parameterspathstring
andfromstring
are required for this operation.test
which runs a test to check if a value (specified by the path parameter) is set in the document. If the test fails, then the patch as a whole will not apply.
#Using the SQL INSERT INTO
Statement
You can add or patch documents using the INSERT INTO
statement in a SQL query, which allows you to
insert the result of a query into a collection. If you SELECT
the _id
field of an existing
document in that query, it will update the existing document rather than add a new document. Learn
more about the syntax and usage of the SQL INSERT INTO
statement here.
Note that this is not recommended and should only be used to perform one-off fixes, as this will inefficiently occupy query execution resources not optimized for data ingest. Instead, we generally recommend that you use the Rockset API to regularly update data in your collections.