Using The Embedded Document Database

The Embedded Document Database provides an easy to use way to store and retrieve data without having to configure or use an external database.

Store Incoming Email Addresses

This example shows how to store and retrieve data using the Embedded Document DB. Suppose we want to maintain a database of incoming email addresses along with a count of how many emails have been received for each address.

We will use a database name 'EmailSenders' with a collection name 'Incoming'. The database & collection will be automatically created when the first record is added.

Each document in the collection will be simple Json:

{
  "Name": "Andrew Bentley",
  "Email": "andrew@ctestcompany.co.uk",
  "LastDate": "2021-09-22T12:14:23Z",
  "LastSubject": "Top 10 new vehicle leasing offers",
  "ReceivedCount": 1
}

Store Emails

// We want to maintain a database of incoming email addresses

ExistingJson=

MessageJson=

// Lookup the existing record (if any)

ExistingJson=Embedded Data StoreEmailSendersSQLSELECT _id,ReceivedCount FROM Incoming WHERE Email = @Email

If%ExistingJson%Is Not BlankThen

// Existing record found. Extract the _id and Received count from the returned Json

ExistingId=Extract FieldFrom%ExistingJson%Json Path"_id"

ExistingCount=Extract FieldFrom%ExistingJson%Json Path"ReceivedCount"

// Increment the received count

NewCount=Increment(%ExistingCount%)

// Update the existing record

Embedded Data StoreEmailSendersSQLUPDATE Incoming SET LastDate = @LastDate, LastSubject = @LastSubject, ReceivedCount = @Count WHERE _id = @ExistingId

// Record updated for %Msg_FromEmail%

Else

// Insert a new record

MessageJson=Create JSON{ "Name": "%Msg_FromName%", "Email": "%Msg_FromEmail%", "LastDate": "%Msg_Date%", "LastSubject": "%Msg_Subject%", "ReceivedCount": 1 }

Embedded Data StoreEmailSenders.IncomingInsert%MessageJson%

// New record added for %Msg_FromEmail%

End If

We first use the Embedded Data Store action to lookup an existing document using a SQL SELECT statement:

SELECT _id, ReceivedCount FROM Incoming WHERE Email = @Email

The @Email parameter value is set to the built-in field %Msg_FromEmail%.

This will return a Json document:

{
  "_id": "614b124213d7e51e08910fd5",
  "ReceivedCount": 1
}

... if a document is found, or blank otherwise.

The results are returned to the %ExistingJson% variable.

If the %ExistingJson% variable is not blank (IE: A document was returned). Then we use the Extract Field action to extract the _id and ReceivedCount fields. We then use the Set action to Increment the %ExistingCount% variable.

We then use the Embedded Data Store action to update the existing document, using a SQL UPDATE statement:

If a document was not returned from the first lookup we insert a new one.

We use the Create Json action to create our Json:

and then the Embedded Data Store action to insert it:

This Automation could be called from an email source (Office 365, Gmail etc). When each new email arrives the EmailSenders database will be updated.

After processing some messages the EmailSenders 'Incoming' collection will contain a document for each unique incoming address along with a count of emails received and the last subject & date.

This database could be queried on any other Automations. For example: to get a list of the top 10 email senders you would use:

SELECT Email FROM Incoming ORDER BY ReceivedCount DESC LIMIT 10

This can be returned as a Json array or as CSV.

---