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:

1. _id holding the _id field (primary key) of the document which is being patched
2. patch 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:

1. 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 parameters pathstring and valueobject are required for this operation.
2. remove which removes the first instance of an object or element of an array (specified by the path parameter). The parameter pathstring is required for this operation.
3. 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 an add operation immediately followed a remove operation. The parameters pathstring and valueobject are required for this operation.
4. 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 parameters pathstring and fromstring are required for this operation.
5. 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 parameters pathstring and fromstring are required for this operation.
6. 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.