Upserts and External IDs
You can insert or update new records through an upsert operation based on the value of a specified external ID field. This identifier is used to determine whether to create a new record or update an existing one. This operation streamlines data synchronization between Rose Rocket and external systems.
Important: The process of upserting records by external IDs in Rose Rocket is uniform across all the objects. In this document, we’ve explained upserts operation using the Commodity object as a representative example. Regardless of the object type–whether it’s Order, Commodity, Tasks, or any other entity– the upsert method remains consistent.
To adapt the examples provided to other objects, you will need to replace the and adjust the JSON payload to match the specific fields and data structure of the object you are working with.
Upserts Behavior
An upsert is performed using PATCH
or POST
requests to the Rose Rocker API with the necessary headers and JSON payload. The externalId field in the payload is used to uniquely identify the record. The behavior of the upsert operation is as follows:
- If the external ID does not match any existing records, a new record is created with the details specified in the request body.
- If the external ID matches an existing record, that record is updated with the new information provided.
Upserting New Records
When integrating with the Rose Rocket API, you may need to insert new records or update existing ones based on a unique external ID. This identifier is used to prevent duplication during the insert operation. If a record with the provided externalId does not exist, the API will create a new record.
Example for Upserting a Record That Doesn’t Yet Exist
This example demonstrates how to use the PATCH method to insert a new record using an external ID. It assumes that an external ID field, such as , has been added to the Commodity object and that no record with the given external ID value currently exists.
Note: This process is the same for all object types within Rose Rocket, you need to replace the and the payload with the relevant information for the object you are working with.
Inserting a new record
To create a new record, send a PATCH
request with the necessary headers and JSON payload. The API will check if a record with the given <externalId>
exists. If not, it will create a new record with the provided data.
Sample Request
curl --location --request PATCH '<http://xyzfreightcorp.roserocket.com/api/v2/platformModel/objects'>
--header 'Content-Type: application/json'
--header 'Authorization: Bearer <token>'
--data '{
"objectKey": "commodity",
"json": {
"externalId": "someCommodityExternalID",
// Additional fields for the commodity object
}
}'
The provided example demonstrates how to perform an upsert operation on any objects within the Rose Rocket platform using a PATCH
request.
- Replace
<token>
with your actual authorization token. - The
<objectKey>
specifies the type of object you are working with, in this case, "commodity". - The
json
object contains the fields that are being provided for the new record, including the<externalId>
.
Sample Response
The successful response will include details of the newly created record, such as its unique identifier and the fields that were provided in the request.
{
"id": "83a57ed2-d517-4c11-a046-f7ed764fc8c7",
// Other fields of the commodity object
"externalId": "someCommodityExternalID",
"createdAt": "2024-04-11T15:09:28.023Z",
"createdBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"updatedAt": "2024-04-11T15:09:28.023Z",
"updatedBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"version": 1
}
- The
<id>
field contains the unique identifier assigned to the new record.
The HTTP status code is 201 (Created).
Error Response
If the upsert operation encounters a server-side issue, the API will return an error response. This error is not specific to duplicate external IDs but indicates a problem with the server's processing of the request:
{
"statusCode": 500,
"message": "Internal server error"
}
Upserting Existing Records
If a record with the provided <externalId>
already exists, the API will update the existing record with the new information provided in the request:
Example for updating the existing records
This example uses the PATCH
method to update an existing record. It assumes that an external ID field, such as <externalIdField>
, has been added to the Commodity object and a record with the given external ID value exists.
To update an existing record, send a PATCH
request with the necessary headers and JSON
payload.
Sample Request
curl --location --request PATCH '<http://xyzfreightcorp.roserocket.com/api/v2/platformModel/objects'>
--header 'Content-Type: application/json'
--header 'Authorization: Bearer <token>'
--data '{
"objectKey": "commodity",
"json": {
"externalId": "someCommodityExternalID",
"freightClass": "75"
}
}'
- Replace
<token>
with your actual authorization token. - The
<objectKey>
specifies the type of object you are working with, in this case, "commodity". - The
json
object contains the fields that are being updated for the record, including the<externalId>
.
Sample Response
The successful response will include details of the updated record, such as its unique identifier and the fields that were updated.
{
"id": "83a57ed2-d517-4c11-a046-f7ed764fc8c7",
"objectKey": "commodity",
"orgId": "e3127dc0-fc4d-4354-b65d-b3e846d80297",
"source": "manual",
"freightClass": "75",
// Other updated fields of the commodity object
"externalId": "someCommodityExternalID",
"createdAt": "2024-04-11T15:09:28.023Z",
"createdBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"updatedAt": "2024-04-11T15:27:28.943Z",
"updatedBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"version": 2
}
- The
<id>
field contains the unique identifier of the existing record that was updated.
Fetching Records With ExternalID
Records with <externalId>
can also be retrieved via the API.
Example for fetching an existing record
This example uses the GET
method to retrieve an existing record. This requires the <objectKey>
and the <externalId>
as path params.
To retrieve an existing record, send a GET
request with the required path params.
Sample Request
curl --location
'<http://xyzfreightcorp.roserocket.com/api/v2/platformModel/objects/><objectKey>/<externalId>/external'
--header 'Authorization: Bearer <token>'
--data ''
- Replace
<token>
with your actual authorization token. - The
<objectKey>
param should be replaced with the objectKey of the record to be fetched e.g. commodity. - The
<externalId>
param should be replaced with the external ID of the record to be fetched.
Sample Response
The successful response will include details of the retrieved record.
{
"id": "83a57ed2-d517-4c11-a046-f7ed764fc8c7",
"objectKey": "commodity",
"orgId": "e3127dc0-fc4d-4354-b65d-b3e846d80297",
"source": "manual",
"freightClass": "75",
// Other updated fields of the commodity object
"externalId": "someCommodityExternalID",
"createdAt": "2024-04-11T15:09:28.023Z",
"createdBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"updatedAt": "2024-04-11T15:27:28.943Z",
"updatedBy": "d0235096-8c31-4212-8b0a-fbf1bd5bd00c",
"version": 2
}
Limitation
Currently, the upsert functionality is not supported with POST
requests. If you attempt to use a POST request for upserting a record with an existing external ID, the operation will not be successful. It is recommended to use the PATCH
method for upsert operations.
Updated about 2 months ago