Automation Action: Azure Cosmos DB

Update or query Json documents from an Azure Cosmos DB.

Specify your Azure Cosmos DB Endpoint and Access Key and click the Connect button to connect.

Enter or select the Database and Container within the selected database.

Enter the Partition Key that you have defined for the Database\Container.

If the database or container do not exist they will be created if the Create option is enabled.

Operation

Insert or Update Document

This option will add a new document or update an existing one.

Set the Document Json to the Json text for the document. If the Json contains an id field then an existing document will be updated if one exists with the same id. If no id field is specified in the Json document the Cosmos will insert a new record and add the id field to the Json stored in the database.

You can build Json using the Create Json action and then set the Document Json to the %variable% name containing the Json, or you can specify it directly.

To store the incoming message you can set the Document Json to %Msg_Json%. The %Msg_Json% built-in variable returns a Json document representing the incoming message (including attachments).

Query Document

This option will query the selected database/container. The returned query results (in Json format) can be assigned to a variable from the Assign To list.

Enter the Query SQL Select Statement, for example:

SELECT *
FROM Families f
WHERE f.id = "AndersenFamily"              

The SQL statement must be compatible with the Cosmos SQL specifications.

You can use %variable% replacements:

SELECT *
FROM Families f
WHERE f.id = "%FamilyId%"              

You can also use parameters:

SELECT *
FROM Families f
WHERE f.id = @id              

If parameters are used you must the assign each parameter name a type and value in the Parameters grid.

When the Automation executes the Json returned from the query will be assigned to the variable specified. If multiple documents are returned the Json will contain an array of documents. If a single document is returned then the Json will be set to the document json.

You can assign the count of documents returned to a variable from the Assign Count To list.