Docs‎ > ‎REST APIs‎ > ‎Data Integration‎ > ‎

MongoDB Integration

Espresso Logic provides explicit support for MongoDB in the Espresso Logic Designer. This capability allows developers to: 

  • Create a MongoDB Resource endpoint
  • Join SQL and MongoDB Collections together and get a single REST API
  • Join two MongoDB collections
  • Adding Request/response events to MongoDB
  • Using Resources in Business Rules
  • Authentication and Access Control to MongoDB resources
  • Advanced access using JavaScript
  • MongoDB and JDBC
MongoDB and SQL API

Create a MongoDB Resource endpoint 

Using the Resource Editor in the Espresso Logic Designer, you create a new Resource by clicking the Add button, and changing the resource name.  Next change the Resource Type drop down to MongoDB.  Enter the remaining fields for the MongoDB Server, Database Name, and collection name.  The username and password are optional (depending on your security settings).  A filter can be passed into the Resource (which we will see in a minute) using MongoDB syntax such as {attributeName: "value"}.  This new Resource "MongoEmployees" is now ready for testing in the REST Lab.


Selecting Attributes to Display

The Attributes tab (for now) works with SQL Tables that are part of the Schema of the current project.  Note that specific columns can be selected, aliased, and formatted and are reflected in the JSON returned by the REST API.  

Using SQL and MongoDB Collections together


Building mobile applications or exposing data to partner systems sometimes require that data from multiple sources be combined, mixed, filtered, and returned in a single request.  This data needs to be presented as a REST API to the mobile front-end to simplify client coding and reduce latency.

In our example, we are asked to return 
  • customer data from our SQL database table Customer

  • with the archived  summary of paid orders, which is a collection named orders stored in MongoDB.  
Using the Resource editor and specification, we describe the data that we want (SQLCustomer is the parent resource and MongoOrders is the child resource).  We create sub-resources attaching (e.g. joining) the sets at child level to create a new resource endpoint.  

In this example, we pass the parent row attribute "<name>" joining the orders field in the Resource (see the Join box (customer_name: "<name>").  Notice the syntax joins the child attribute to the parent value for each row returned from the parent.   The filter is used to refine the selection to only the paid orders.


Define the SQLCustomer Resource first

Add the sub-resource MongoOrders using MongoDB resource type  


  REST Lab - testing our new resource SQLCustomer with MongoDB orders

Join two MongoDB collections 

MongoDB developers are told that you cannot do joins across collections.  In a strict language sense, this is true.  MongoDB is not a relational database.  In Espresso Logic, resources are created dynamically and incrementally so a parent resource can pass detail row information to a child to retrieve a sub-resource of another collection.  

For example, we have one collection in MongoDB named department.  We have been asked to return a new blended resource that includes these department records and employees that work in the department. These collections can be from different databases or even different servers.  

The 'join' passes selected attributes from the parent to the child resource to create a 'linked' collection of related values in the sub-resource.  This allows you to do joins across MongoDB-to-MongoDB, MongoDB to SQL, SQL to MongoDB, and MongoDB to external REST for a blended mix and match of complex endpoints.  Note the Syntax of the Join is expressed as a MongoDB expression: {department_name: "<name>"} where <name> is passed from the department collection.



Attach Path

In the example above - when we join a MongoDB collection - we need to tell the Resource logic where to insert this sub-resource collection.  If the name is unique; then the result is a new attribute array with the result.  If the name is an existing attribute array - the collection is inserted inside that array.


Keys


This optional field can specify which keys (or attributes) should be included in the MongoDB resource.A simple example: if your Mongo resource has attributes nameage and balance, and you want to include only attributes name and balance, you can use either:
	{name: 1, balance: 1}
or you specify the opposite, i.e. do not include the age attribute:
	{age: 0}
This field can actually do quite a bit more than that -- it is passed to MongoDB as the second parameter of the find method. For instance, you could use Mongo's projection capabilities, e.g. :
	{ coordinates: {$slice: 2}}
which would return only the first values from the array named "coordinates".

Filter

This is a way to specify additional filtering on the resource, e.g. if you want to restrict the rows that should be returned by the resource.For SQL resources, this should simply be a fragment of a where expression;
         
   amount_total < 1000 and paid = 'Y'
For MongoDB resources, you should use the Mongo syntax:
          
 :{"$and": [{"$lt": {"amount_total": 1000}}, {"paid": "Y"}]}




REST Lab Example - GET MongoDepartment with EmployeesInDepartment

[
  {
    "_id": {
      "$oid": "53d64c02a32268822c09e5df"
    },
    "name": "Euro Sales",
    "managed_by": null,
    "head_department_name": "Sales",
    "sum_sub_department_budget": 400,
    "budget": 200,
    "budget_with_sub_department_budget": 600,
    "notes": "Dept Notes - Euro Sales",
    "secret_agenda": "Agenda - Euro Sales",
    "ts": {
      "$date": "2014-07-26T11:16:32.000Z"
    },
    "employees": [
      {
        "_id": {
          "$oid": "53d64c03a32268822c09e5e9"
        },
        "name": "Sami Stoner",
        "base_salary": 44000,
        "employee_type": "salesrep",
        "ts": {
          "$date": "2014-07-26T11:16:32.000Z"
        },
        "visible_to": null,
        "department_name": "Euro Sales",
        "on_loan_department_name": null,
        "notes": "on a high"
      }
    ]
  }
]

Adding Request/Response events to MongoDB 




Using the Espresso Logic additional pre-and post-event handling services can be added for your MongoDB resources. Event handling begins with incoming requests - the Logic Design Studio offers a JavaScript editor and access to internal request state information (e.g. a GET, PUT, POST, DELETE).   

  • request event is invoked right after a request has been received, but before any processing work has begun. Your request handler will have access to all the relevant information, such as which API key is being used, what the request verb is (GET, POST, PUT,DELETE.), the JSON payload for POST and PUT, and the URL parameters.
  • response event is invoked after the request has been processed, but before the response is sent back to the client. Additional work cam be performed to modify the response.
See Request Events page for samples.

A Resource Row Event is a piece of JavaScript that gets executed every time a row is retrieved from the database for a specific resource or sub-resource.

This JavaScript code can then manipulate the row. See this link for more details: row event

Business logic is triggered by PUT, POST, and DELETE changes to SQL data only which in turn can call MongoDB resources to include in calculations, validations, constraints, and decision support.

Using Resources in Business Rules

In the blended architecture of the cloud - companies are turning to tools like MongoDB to build part of their new web and mobile strategy.  
  • Using read-only high-speed MongoDB resources for returning collections needed for web-page display (images, documents, summary data, user-profile settings, logs, audits.) is the query response needed for part of a mobile service.  

  • For transaction commands that involve payments or multi-table SQL updates with logic the responsibility falls to Espresso Logic to handle the optimistic locking, optimized multiple table updates, business rule processing, and synchronization with external systems.   
  • Business Rules are triggered when state change occurs (PUT/POST) on SQL (JDBC) tables.  During the processing of rules (just like a spreadsheet) - other rules, columns, and tables may be dependent on these changes.  The rules event life cycle allows the introduction of 'Resources' to be included in and participate with existing expressions.  

Authentication and Access Control 

Espresso Logic offers multiple authentication services including:  LDAP, Windows Azure AD, and Stormpath. In addition, the Role-Based Access Control service feature gives fine-grain control to determine who can see which resource  based on role assignment.

0

                     Each Role can have access rights to specific resource endpoints. 

                

Advanced access to MongoDB using JavaScript


The native MongoDB Java driver can be used to write advanced (native) JavaScript.  In this example, we get the orders for a specific customer (containingRow) and passing this to MongoDB directly and returning the JSON response.  Espresso Logic has add a JavaScript wrapper library around the MongoDB Java driver to make the process of using JavaScript easier.  See the MongoUtility link.



MongoDB and JDBC (Experimental)

There are several JDBC drivers that can connect to MongoDB and perform selects, joins, order by, group by, and filtered where clauses.  This example is shown using one of these drivers.  The JDBC driver is loaded using the Project/Library upload facility to add new driver JAR files and optionally any JavaScript into the system.  This example is using a JDBC driver to connect to MongoDB and execute an SQL query with a where clause.  The JDBC driver can also do joins, sorts, column selection and some drivers allow selection into nested arrays (e.g Customer.Orders.Items.PartNum = 12345);