MongoDB Integration

  • Create a MongoDB resource type.
  • Join SQL and MongoDB Collections together and get a single REST API.
  • Join two MongoDB collections.
  • Add request/response events to MongoDB.
  • Use resources in business rules.
  • Authenticate and control access to MongoDB resources.
  • Have advanced access using JavaScript.
MongoDB resources return filtered requests in JSON format using pipeline syntax. This can be a stand-alone top resource on joined (linked) to SQL using the pipeline syntax ({customer_name: "<name>"}. The <name> represents the parent SQL attribute being passed into the JOIN).

Create a MongoDB Resource

You can test the "MongoEmployees" resource type in the REST Lab.

For more information about how to test resource types, see Test your API Using the REST Lab.

  1. On the Create, Resources, Resource tab, create a new resource by clicking New Resource.
  2. Complete the following fields and save your changes:
    Resource type
    Select MongoDB. MongoDB resource connect to Mongo objects.
    Resource name
    The name for your resource.
    Mongo server
    The name of the Mongo server.
    Mongo database name
    The name of the Mongo database.
    Mongo user name
    (Optional depending on your security settings) The Mongo user's username.
    Mongo password
    (Optional depending on your security settings) The Mongo user's password.
    Mongo collection name
    The name of the Mongo collection.

The MongoDB resource is created.

Pass Filters into MongoDB Resources

You can pass a filter into the resource using MongoDB syntax such as {attributeName: "value"}. Enter the filter details into the Filter field on the Create, Resources, Details tab. The following image shows the field on this page:

Select Attributes to Display

The Create, Resources, Attributes tab works with SQL tables that are part of the schema of the current project. You can select, alias, and format specific columns. The columns are reflected in the JSON returned by the REST API. The following image shows this tab:

Use 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 this example, you must return customer data from your SQL database table Customer with the archived summary of paid orders, which is a collection named orders stored in MongoDB. The data that you want (SQLCustomer is the parent resource and MongoOrders is the child resource) is described. You create subresources attaching (for example, joining) the sets at child level to create a new resource type.

In this example, the parent row attribute "<name>" is passed joining the orders field in the resource (see the Join box (customer_name: "<name>"). 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

The following image shows how to define the SQLCustomer resource:

Add the MongoOrders Sub-resource Using the MongoDB Resource Type

The following image shows how to add the MongoOrders sub-resource using the MongoDB resource type:


Test the new resource SQLCustomer with MongoDB orders using the REST Lab

{
    "name": "Alpha and Sons",
    "balance": 4484,
    "credit_limit": 9000,
    "@metadata": {
      "href": "http://localhost:8080/APIServer/rest/el-local/demo/v1/CustomerJoinMongoPurchaseOrders/Alpha%20and%20Sons",
      "checksum": "A:e86aea2e0a4e74bf"
    },
    "MongoOrders": [
      {
        "_id": {
          "$oid": "53d64c59a32268822c09e999"
        },
        "order_number": 6,
        "amount_total": 108,
        "paid": false,
        "notes": "Pack with care - fragile merchandise",
        "customer_name": "Alpha and Sons",
        "salesrep_id": 7,
        "items": [
          {
            "_id": {
              "$oid": "53d64c39a32268822c09e750"
            },
            "lineitem_id": 11,
            "product_number": 2,
            "qty_ordered": 2,
            "product_price": 25,
            "amount": 50
          },

Join MongoDB Collections

MongoDB developers are told that they cannot do joins across collections. In a strict language sense, this is true. MongoDB is not a relational database. In API Creator, 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, you have a collection in MongoDB named department. You must 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 resource types.

Note: The syntax of the join is expressed as a MongoDB expression: {department_name: "<name>"}, where <name> is passed from the department collection.

The following image shows this example on the Create, Resources, Resource tab:

Attach Path

In the previous example, when you join a MongoDB collection, you 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

You can specify which keys (or attributes) should be included in the MongoDB resource by entering the key in the Mongo keys field on the Create, Resources, Details tab:

Example

If your Mongo resource has attributes name, age 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, that is, do not include the age attribute:

{age: 0}

This field is passed to MongoDB as the second parameter of the find method. For instance, you could use Mongo's projection capabilities, for example:

{ coordinates: {$slice: 2}}

which returns only the first values from the array named "coordinates".

Filter

This is a way to specify additional filtering on the resource, for example, if you want to restrict the rows that should be returned by the resource. For SQL resources, this is 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"
      }
    ]
  }
]

Add Request/Response Events to MongoDB

You can add pre-and post-event handling services for your MongoDB resources using API Creator. Event handling begins with incoming requests. The JavaScript editor and access to internal request state information (for example, a GET, PUT, POST, DELETE):

  • A request event is invoked right after a request has been received but before any processing work has begun. Your request handler has 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.
  • A response event is invoked after the request has been processed but before the response is sent back to the client. You can modify the response.

For more information about viewing samples, see Event Handlers.

The following image shows adding request/response events to MongoDB on the Manage, Request Events page:

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.

For more information about row events, see Resource Row Events.

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.

Use 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 Live API Creator 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 

You can use the LDAP, Microsoft Azure Active Directory (Azure AD), and SQL authentication services for authentication. In addition, role-based access security provides authorization control over what resources, rows, and columns authenticated users can access.

For more information about authentication and access control, see Role-Based Endpoint Access.

Advanced Access to MongoDB using JavaScript

You can write advanced (native) JavaScript using the native MongoDB JDBC driver. In the following example, the orders for a specific customer (containingRow) are passed to MongoDB directly and the JSON response is returned. API Creator includes the MongoUtility.js JavaScript library, which is a JavaScript wrapper library around the MongoDB JDBC driver. This library makes the process of using JavaScript easier.

For more information about the MongoUtility.js JavaScript library, see MongoUtility.

The following image shows the Create, Resources, Details tab:

MongoDB and JDBC (Experimental)

You can connect to MongoDB and perform selects, joins, order by, group by, and filtered where clauses using a JDBC driver.

Note: You add new driver JAR files, and optionally any JavaScript, into API Creator on the Create, API Properties, Libraries tabs.

The following example connects to MongoDB and executes an SQL query with a where clause using a JDBC driver:

You can do joins, sorts, column selection using some JDBC drivers and select into nested arrays (for example, Customer.Orders.Items.PartNum = 12345) using others.