Guides

Upserts and External IDs

Suggest Edits

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 5 months ago