Define MongoDB Resource Types

Define MongoDB Resource Types
lac42
MongoDB resources are a type of resource that you can explicitly define within your API. They return filtered requests in JSON format using the MongoDB pipeline syntax. MongoDB resources can be stand-alone top resources on joined (linked) to SQL.
In this article:
2
Verify the Prerequisite
Before you can create a MongoDB resource, ensure that you have installed the MongoDB driver.
For more information about how to install the MongoDB driver, see MongoDB Data Source.
Create a MongoDB Resource
Prerequisites: 
You know your MongoDB connection information.
Follow these steps:
  1. With your API open, in the Create section, click 
    Resources
    .
    If you have not defined any resources yet, the Welcome to Resources page appears. If you have created at least one resource, the Resource tab displays.
  2. Do one of the following options:
    • If you have not defined a resource yet, create a resource by clicking 
      Create a New Resource
      .
    • If you have created at least one resource, click 
      New Resource
      .
    The Add Resource window opens.
  3. Complete the following fields, and then click 
    Add
    :
    Resource Type
    Defines the type of resource that you want to create. Select 
    MongoDB Resource
    .
    Values:
    • Ⓣ Table-based Resource.
       Resources that are linked to existing base SQL entities (tables and views) and automate SQL handling. You can integrate other data sources and control SQL by defining the resource type.
    • Ⓢ Free SQL
       Resource
      . Resources that you specify the SQL to execute.
    • Ⓙ JavaScript
       Resource
      . (Advanced users) Resources that you supply the server-side JavaScript that is executed whenever the resource is accessed and returns JSON.
    • Ⓜ MongoDB
       Resource
      . Resources that you can connect to a specific MongoDB server, database, and collection.
    Default:
     Table-based if you have defined a data source or JavaScript if there are no defined data sources.
    Resource Name
    The resource name is the container name with JSON. The name must contain alphanumerics or the underscore (_) special character.
    Unique:
     Yes
    Your MongoDB resource is created and displays in the list of resources.
  4. Complete the following fields on the Resource tab, and then save your changes: 
    Mongo server
    The name of the MongoDB server. 
    Mongo database name
    The name of the MongoDB database.
    Mongo user name
    The MongoDB user's username.
    Optional:
     Yes, depending on your security settings.
    Mongo password
    The MongoDB user's password.
    Optional:
     Yes, depending on your security settings.
    Mongo collection name
    The name of the MongoDB collection.
    Filter
    Passes a filter into the resource. Enter a filter that you want to pass into this resource using the following MongoDB syntax. Use double quotes ("") and proper JSON syntax:
    {"attributeName": "value"}
    or
    {"$and": [{"name": {"$eq": "Alpha"}}, {"$gt": {"amount": 100}}]}
    Attach Path
    When you join a MongoDB collection, you must tell the resource logic where to insert this subresource 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, then the collection is inserted inside that array.
    Required:
     Yes for subresources of a Mongo resource.
    Mongo keys
    Specifies the keys to include in the MongoDB resource. You can specify which keys (or attributes) should be included in the MongoDB resource.
    Example
    Your MongoDB resource has the 
    name
    age
    , and 
    balance
     attributes and you want to include only the
    name
    and
    balance
    attributes, you can specify one the following:
    • Include the age attribute:
      {"name": 1, "balance": 1}
    • Exclude the age attribute:
      {"age": 0}
      The
      age
      attribute is passed to MongoDB as the second parameter of the
      find()
      method. For instance, you can use MongoDB projection, for example:
      { coordinates: {$slice: 2}}
      This example returns only the first values from the
      coordinates
      array.
    Order
    Specifies row ordering on the resource. For example, sort customers by balance.
    This list is passes to the database. If your database requires it, quote column names. For example,
    "MyColumn" desc, "MyOtherColumn" asc
    .
    Required:
     Yes for subresources of a Mongo resource.
The MongoDB resource is created.
Select the Attributes to Display
The resource attribute values that are displayed work with the SQL tables that are part of the schema of the current API. You can select, alias, and format specific entities (tables or views) and columns. The columns are reflected in the JSON that the REST API returns.
Define How you Want
CA Live API Creator
 to Handle Incoming Requests for your Resource
You can provide options on how you want 
CA Live API Creator
 to handle incoming requests for your resources by defining the extended properties for the resource.
For more information about how to define the extended properties for a resource, see Manage the Extended Properties for Resources.
Join SQL and MongoDB Collections Together
You can join SQL and MongoDB collections together and get a single REST API. 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 Customer SQL database table 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 subresource) is described. You create subresources, attaching (for example, joining) the sets at the child level to create a resource.  The join passes the 
[name]
 parent row attribute and joins the orders field in the resource (see the Join box {"
customer_name": "[name]"
). 
Use the following process to join SQL and MongoDB collections together:
  1. create3. Enter 
    LogicDemo
     as the Mongo database name and 
    This is the root resource using the Customer SQL table
     as the description.
Add the MongoOrders Subresource Using the MongoDB Resource Type
  1. With the
    SQLCustomer
     MongoDB resource selected, click 
    New Level
    .
  2. Complete the following fields, and then click
    Add Level
    :
    Resource Type
    Select 
    MongoDB Resource
    .
    Resource Name
    The name of this subresource. Enter 
    MongoOrders
    .
    Join
    The syntax joins the child attribute to the parent value for each row returned from the parent. Enter 
    { "customer_name": "[name]"}
    .
    The Resource page appears.
  3. Complete the following fields, and then save your changes:
    Mongo server
    Enter the name of the MongoDB server.
    Mongo database name
    The name of the MongoDB database. Enter 
    LogicDemo
    .
    Mongo user name
    The MongoDB user's username.
    Optional:
     Yes, depending on your security settings.
    Mongo password
    The MongoDB user's password.
    Optional:
     Yes, depending on your security settings.
    Mongo collection name
    The name of the MongoDB collection. Enter 
    purchaseorder
    .
    Join
    Enter 
    {"customer_name": "<name>[name]"}
    .
    Filter
    Specifies additional filtering on the resource. The filter is used to refine the selection to only the paid orders. Enter 
    {paid: true}
    . For example, if you want to restrict the rows that should be returned by the resource, use the following syntax:
    {"$and": [{"$lt": {"amount_total": 1000}}, {"paid": "Y"}]}
    Attach path
    When you join a MongoDB collection, you must tell the resource logic where to insert this subresource 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, then the collection is inserted inside that array.
    Required:
     Yes for subresources of a Mongo resource.
The MongoOrders subresource is added.
Test the SQLCustomer Resource with the MongoOrders Subresource using the REST Lab
You can test the 
MongoEmployees
resource in the REST Lab.
 For more information about how to test resource types, see Test your API 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. API Creator creates resources dynamically and incrementally so a parent resource can pass detail row information to a child to retrieve a subresource of another collection. For example, you have a collection in the
MongodDepartment
MongoDB resource. You must return a new blended
EmployeesInDepartment
 MongoDB subresource 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 subresource to create a linked collection of related values in the subresource. In this way you can 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.
Join the MongoDB collections using the
Join
and
Attach path
fields.
The pipeline syntax of the join is expressed as a MongoDB expression:
{"department_name": "[name]"}
where
[name]
represents the parent SQL attribute being passed from the department collection into the JOIN.
REST Lab Example
GET
MongodDepartment
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 an Event Handler for your MongoDB Resource
You can add pre-and post-event handling services for your MongoDB resources. Event handling begins with incoming requests. The JavaScript editor can access internal request state information (for example, a GET, PUT, POST, DELETE). PUT, POST, and DELETE changes to SQL data trigger business logic. The business logic can call MongoDB resources to include in calculations, validations, constraints, and decision support.
For more information about when API Server calls event handlers, about viewing request event samples, and how to create a request or response event, see Event Handlers.
Follow these steps:
  1. With your API open, in the Manage section, click 
    Request Events
    .
    A list of events display.
  1. Above the events list, click 
    Add
    .
  2. Complete the fields on the page:
    Event type
    Defines the request event type. Accept the default selection.
    Options: 
    Request event and Response event
    Default:
     Request event
    Event Handler name
    The name for your event handler. Enter 
    RequestEventForMongo
    .
    Code
    The operations for this event handler. Add the following code into the code editor:
    if ("GET" == req.verb && "SWLCustMongoOrders" == req.resourceName ){
       log.debug("Request Event GET for MongoDB resource named SWLCustMongoOrders");
    }
Your event handler for a MongoDB resource is created.
Write Custom JavaScript Code
You can write a JavaScript function or subresource and call the MongoDB Java libraries, for example:
log.debug("MongoDB is now starting");
//declare the Java Libraries we need to access
var MongoClient = Java.type("com.mongodb.MongoClient");
var ServerAddress = Java.type("com.mongodb.ServerAddress");
var DB = Java.type("com.mongodb.DB"); 
var MongoDatabase = Java.type("com.mongodb.client.MongoDatabase");
var MongoCollection = Java.type("com.mongodb.client.MongoCollection");
var Properties = Java.type("java.util.Properties");
var ArrayList = Java.type("java.util.ArrayList"); 
var serverAddress = "localhost";
var dbName = "customer";
var collectionName = "orders";
var client = new MongoClient(serverAddress);
var db = client.getDatabase(dbName);
if (db === null) {
throw "Invalid MongoDB database name: " + dbName;
}
var coll = db.getCollection(collectionName);
if (coll === null) {
   throw "MongoDB Collection " + collectionName + " does not exist in selected database";
var db = client.getDatabase("cusomter");
var coll = db.getCollection("orders");
log.debug("coll "+coll);
var cursor = coll.find().limit(10).iterator();
log.debug("cursor "+ cursor);
var objects = [];
while (cursor && cursor.hasNext()) {
   objects.push(cursor.next().toJson());
}
return {result: objects};
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 that companies need for part of a mobile service.
For transaction commands that involve payments or multi-table SQL updates with logic, the responsibility falls to
 
CA Live API Creator
 
to handle the optimistic locking. 
CA Live API Creator
 optimizes multi-table updates, business rule processing, and synchronization with external systems. It triggers business rules when state change occurs (PUT/POST) on SQL (JDBC) tables. During the processing of rules (just like a spreadsheet), other rules, columns, and tables might be dependent on these changes. You can include resources in expressions or have them participate with expressions using the rules event lifecycle.