Automation Action: Embedded Data Store

Insert, Update and Query data using the embedded document DB. ThinkAutomation includes an embedded server-less document database that you can use to store any arbitrary data in Json format and then later query the data using SQL statements. The Embedded Data Store makes it easy to store and retrieve data in your Automations without having to configure a database. See: Embedded Document DB Notes.

If you only need to store single values against a key (key/value pairs) then you can use the Embedded Value Store action which provides simple storage and retrieval of key/value pairs.

Database Name

Any number of separate databases can be created. Database names can contain letters or numbers only. You can use a %variable% replacement for the database name (all none alpha numeric characters will be removed during execution). Databases are global to the ThinkAutomation instance (IE: The same database can be used on all Solutions/Automations). A database is automatically created when it is first accessed.

Password

A database can be optionally assigned a password. If a password is specified then the database file will be encrypted with AES encryption. You cannot change a database password after it has been created.

Collection Name

A database can contain multiple collections. A 'collection' is similar to a 'table' in a traditional database. Collection names can contain letters or numbers only. You can use a %variable% replacement for the collection name (all none alpha numeric characters will be removed during execution).

Operations:

Insert

Inserts a new document into the specified database/collection. Specify the Document Json. This can contain %variable% replacements - or be a single %variable% containing Json created from a previous action. If you are using the Database Message Reader then you can use %Msg_Body% since this will already contain Json read from the source database.

If you want to easily store all extracted field values you can use the %Msg_ExtractedFieldsJson% built-in variable. This will return a Json document containing each extracted field name & value.

Each document must have an '_id' field containing a unique id. This field will be added automatically with a unique value if it is not included in the Json. If an '_id' field is included in the Json and its value is not blank then the existing document will be updated if it exists, otherwise a new document will be inserted (upsert).

The '_id' field is automatically indexed. The unique id will be returned after the insert. Select the variable to receive the new Id from the Assign _id To list.

The Ensure Indexed entry allows you to optionally define one or more fields as additional indexes (separated by commas). This will enable faster queries. For example, suppose we want to insert the following:

{
  "isbn": "123-456-222",
  "author": {
    "lastname": "Doe",
    "firstname": "Jane"
  },
  "editor": {
    "lastname": "Smith",
    "firstname": "Jane"
  },
  "title": "The Ultimate Database Study Guide",
  "category": [
    "Non-Fiction",
    "Technology"
  ]
}              

We could set the Ensure Indexed entry to 'isbn,author.lastname'. This will ensure both the "isbn" and "author.lastname" fields are indexed.

Update

Replaces an existing document. The Document Json should contain the new document. The Where _id Equals should be set to the _id of the document to replace. The Assign _id To variable will receive the _id - or be set to blank if the existing document was not found.

You can also use the SQL operation to update specific fields, for example:

UPDATE books SET editor.firstname = 'Jane', editor.lastname = 'Doe' WHERE isbn = '123-456-222'              

Delete

Deletes an existing document. The Where _id Equals should be set to the _id of the document to delete. The Assign _id To variable will receive the _id - or be set to blank if the existing document was not found.

Get

Retrieves a single document. The Where _id Equals should be set to the _id of the document to retrieve. The Assign _id To variable will receive the document Json - or be set to blank if the existing document was not found.

SQL

You can execute SQL statements against a Database. SELECT, UPDATE & DELETE can be used. The Collection Name entry is not required when using the SQL option, since the collection name will be specified in the SQL statement itself.

You can use SELECT * FROM to return full documents, or SELECT FieldName,FieldName2... FROM to return only specific fields.

Parameters can also be used. For example:

SELECT _id,title,isbn,author.lastname FROM books WHERE author.lastname = @Name
ORDER BY author.lastname LIMIT 10              

If a @parameter is specified in the SQL statement then you must set its type and value in the Parameters grid. Parameter values can be set to %variable% replacement values.

When using a SELECT statement the returned documents can be returned as Json or CSV. If returning Json then a Json array will be returned if the SELECT statement returns more than 1 document.

When using an UPDATE or DELETE statement then returned value will be the number of documents affected.

Select the variable to receive the results from the Assign To list. The results can be returned as Json, CSV (without headers) or CSV (with headers).

You can also use the regular Lookup From A Database action to perform a lookup using the Embedded Document DB. This action allows specific column values to be assigned to variables. The Update A Database Using Extracted Fields action can also be used to update (insert or update) the Embedded database.

Drop Collection

Deletes all documents in the specified Database / Collection.

The Assign To variable will receive the dropped collection name or blank if the collection does not exist.

Drop Database

Deletes the specified Database.

The Assign To variable will receive the dropped database name or blank if the database does not exist.