Javascript and Freemarker API for Collections

Table of Contents

• Javascript and Freemarker API for Collections
  • Presentation
    • Generalities
    • Collection
    • Object
    • Aggregation
    • Create a collection
    • Create the web interface to display content of your collection
    • Javascript methods for collections
      • Add an object
      • List objects
      • Update objects
      • Update specific fields in objects
      • Delete objects
      • Aggregation
    • Freemarker functions for collections
      • Add an object
      • Mass import objects from a csv file
      • List objects
      • Load a single object
      • Count objects
      • Create collection
      • Drop a collection
      • Delete a collection
      • Rename a collection
      • Update objects
      • Update specific fields in objects
      • Delete objects
      • Remove a specific field in objects
      • Inject objects in Freemarker variables
      • Aggregation
    • Advanced queries
      • Filter using regular expression
      • Filters on arrays
      • Sort items in a report
      • Aggregation in a report
      • Best practices

Presentation

Generalities

RunMyProcess collections allow RunMyProcess web interfaces and processes to create, use and persist user defined objects.

Objects are stored in collections, a collection is specified using its name.

Storage engine used is MongoDB.

Collections can be accessed from web interfaces using Javascript functions, from processes using Freemarker functions and outside the platform using a REST API.

Although all customers can use collections, some limitations apply : a collection can not be bigger than 10 MB, a customer can not use more thant 10 collections, and an object in a collection can not be bigger than 1MB. If you need to use more collections and/or bigger objects simply contact our support. You can always see the current size of a collection in the Collections tab of the project which the collection belongs to.

Collection

A collection is a group of objects of the same type. For example you can have a collection of clients, a collection of shops and/or a collection of products.

Even though each object in a collection does not have to adopt the same structure, it's recommended for performance reason.

A collection needs to be configured before use, the configuration takes place at the project level.

Object

An object is an item in a collection. An object is a structured set of fields. To create an object, you just have to create a structured variable in your web interface (javascript) or process (freemarker). This object will be represented as a json structure.

Aggregation

An aggregation is an operation that can be performed on a data structure (collection) in order to obtain different calculations according to a certain data grouping. Aggregations can be applied directly on a collection (using freemarker, javascript) or on a collection report (from a web interface).

There are two concepts to understand about aggregation:

• Pipelines
  • $match : To filter data according to a specified set of fields in your collection.
  • $group : To define the keys of your grouping.
  • $project : To define the final fields you want to project in your result and their names.
  • $sort : To sort the result according to a specified set of fields.
  • $limit : To limit the number of results.
  • $skip : To skip to a given position in the result set.
• Expressions
  • $sum : Returns the sum of all the values of the specified field according to the grouping
  • $avg : Returns the average of all the values of the specified field according to the grouping.
  • $min : Returns the minimum value of the specified field according to the grouping.
  • $max : Returns the maximum value of the specified field according to the grouping.

Technically, we can define an aggregation as a set of pipelines:

[{pipepline1},{pipepline2},{pipepline3},{pipepline4}]

More information on MongoDB documentation.

This tutorial will show you how to display the content of a collection using collection report, how to use javascript methods, and how to use freemarker functions in the processes.

Create a collection

• Create a new project clients
• Go to Collections tab and click New collection
  • Name : clients
  • Save

You should see :

Note: when you create a new collection, you actually create one collection per process execution mode, so that LIVE data will not be mixed with TEST data. If you open your web interface in LIVE mode, and get objects from your collection, the source will be the LIVE collection.
If you open your web interface in TEST mode, and get objects from your collection, the source will be the TEST collection.
This is the same for processes. LIVE and TEST data are always separated. This behaviour allows you to debug your application with no impact on the LIVE data.

Create the web interface to display content of your collection

• Create a new web interface manage clients
• Insert a Report widget (27/01/2014 12:12:03 te)
  • Choose the collection clients by clicking the Choose a collection button (1)
  • Configure the report columns by clicking on the wrench icon (2)

• Configure 3 columns in the report :
  • firstname property - First Name header
  • lastname property - Last Name header
  • phone_number property - Phone Number header

Now let's configure your web interface to be able to handle collection objects using javascript methods

• Go to the Collections tab (1)
  • Click on Add a collection (2)
  • Choose the clients collection (3)
  • Check that the javascript identifier is col_clients (4)

• Save your web interface and preview it

The first time you preview the web interface, the report should be empty since the collection contains no object. Later, to refresh the report, hit Reset filters.

In TEST mode, you can use the JS tester console in order to add some objects, by using the appropriate js methods.

Javascript methods for collections

Add an object

To add an object to a collection, you can use the saveCallbackmethod.

id_collection.saveCallback(object,callbackSuccess,callbackFailure)

This method expects three parameters : the object to save into the collection, a callback that will be called in case of a success and a callback that will be called in case of a failure.

Example:

function add_ok(result) {
alert("ok " + JSON.stringify(result));
}

function add_ko(result) {
alert("ko " + JSON.stringify(result));
}

var my_object = {};
my_object.firstname = "Mike";
my_object.last_name = "Tyson";
my_object.phone_number = "";

col_clients.saveCallback(my_object,add_ok,add_ko);

when you execute this script, you should get a pop up :

After the code execution, a new object has been added to the collection and since this action was successful, the success callback (add_ok) has been called. The result parameter of the callback, is the saved object, as seen by the mongoDB engine.

You can of course customize your callbackSuccess and callbackFailure functions as you wish.

List objects

id_collection.listCallback(pattern,options,callbackSuccess,callbackFailure)

This method expects four parameters : the pattern used to find matching objects, some options passed using a json object, a callback that will be called in case of a success and a callback that will be called in case of a failure. If you want to list all objects of a collection, use {} as a pattern.

Example 1:

function list_ok(result) {
    alert("ok=" + JSON.stringify(result));
}

function list_ko(result) {
    alert("ko=" + JSON.stringify(result));
}

var my_pattern = {};
col_clients.listCallback(my_pattern,{},list_ok,list_ko);

You should get all the objects:

ok=[{"firstname":"Mike","lastname":"Tyson","phone_number":""}]

Example 2:

var my_pattern = {};
my_pattern.firstname = "John";

col_clients.listCallback(my_pattern,{},list_ok,list_ko);

You should get all the objects whose firstname field is "John", for example :

ok=[{"firstname":"John","lastname":"Smith"},{"firstname":"John","lastname":"Doe"}]

The options parameter has several properties:

var options = {};

options.fields = ["firstname","lastname"];// retrieve objects with only   firstname and lastname fields. Default is [] to get all fields
options.mode = "TEST"; // retrieve data from the TEST or the LIVE collection.     Default is current mode of the web interface
options.nb = 5; // max nb of items to list. Default is 100. Max is 1000.
options.first = 2; // starting index to list objects. Default is 0
options.orderby = ["lastname","firstname"]; //order by lastname and firstname. 
options.order = ["asc","desc"]; //must match `orderby` field : order by lastname asc and firstname desc.
options.asynchronous = false; //if you need synchronous requests, must be set as false. Default is true

Update objects

id_collection.updateCallback(pattern,object,callbackSuccess,callbackFailure)

It means : 'update the first object matching pattern with the document object'

id_collection.updateMultiCallback(pattern,object,callbackSuccess,callbackFailure)

It means : 'update all the objects matching pattern using the operations specified in object. Be very careful when using this method, for you can update a lot of object documents at once.

Example:

function update_ok(result) {
alert("ok " + JSON.stringify(result));
}

function update_ko(result) {
alert("ko " + JSON.stringify(result));
}

var my_pattern = {};
my_pattern.firstname = "John";
my_pattern.lastname= "Smith";

var my_object = {};
my_object.firstname = "John";
my_object.lastname = "Smith";
my_object.phone_number = "111-222-333";

col_clients.updateMultiCallback(my_pattern,{$set:my_object},update_ok,update_ko);

You should get a popup:

only 1 object matched the pattern and has been updated in this case.

Update specific fields in objects

To update only specific fields without passing all the object again, you can use the {$set:object} operation.

Example:

I want to update only the phone_number field for objects with firstname = "John"

function update_ok(result) {
alert("ok " + JSON.stringify(result));
}

function update_ko(result) {
alert("ko " + JSON.stringify(result));
}

var my_pattern = {};
my_pattern.firstname = "John";

var my_object = {};
my_object.phone_number = "444-555";

col_clients.updateMultiCallback(my_pattern,{$set:my_object},update_ok,update_ko);

If I had 2 John in the collection, I would get:

Delete objects

id_collection.removeCallback(pattern,callbackSuccess,callbackFailure)

It means : 'delete all the objects matching pattern'

Example:

function delete_ok(result) {
alert("ok " + JSON.stringify(result));
}

function delete_ko(result) {
alert("ko " + JSON.stringify(result));
}

var my_pattern= {};
my_pattern.firstname = "John";
my_pattern.lastname = "Smith";

col_clients.removeCallback(my_pattern,delete_ok,delete_ko);

Aggregation

In order to perform an aggregation on a collection, you need to use the following expression:

id_collection.aggregateCallback(my_pipelines,my_options,callbackSuccess,callbackFailure);

Example:
Here's the content of my collection :

Where fields for every item are [first_name,last_name,state,nb_of_cars,nb_of_motors]

Let's say we want to display the number of cars per state. We have to build our aggregate expression and use a $group pipeline. The grouping key should be the state. The grouping key is passed through the _id object. We will use the $sum function to calculate the total number of cars per state.

{"$group":{"_id":{"State":"$state"},"Total":{"$sum":"$nb_of_cars"}}}

$group,_id,$sum are reserved words. $state,$nb_of_cars are the name of the fields in the collection.

Now we have to define which fields we want to project and show in the final result. Therefore, we have to use the $project pipeline.

{"$project":{"STATE":"$_id.State","TOTAL_CARS":"$Total"}}

As shown above, State must be prefixed by $_id as it's part of the grouping key.

The final code will be :

function callbackSuccess(result) {
alert("OK " + JSON.stringify(result));
}
function callbackFailure(result) {
alert("KO " + JSON.stringify(result));
}

var my_pipelines = [{
"$group" : {
"_id" : {
"State" : "$state"
},
"Total" : {
"$sum" : "$nb_of_cars"
}
}
}, {
"$project" : {
"STATE" : "$_id.State",
"TOTAL_CARS" : "$Total"
}
}
];

id_collection.aggregateCallback(my_pipelines,{},callbackSuccess,callbackFailure);

Here's the callbackSuccess result:

[{"STATE":"Arizona","TOTAL_CARS":4},{"STATE":"Florida","TOTAL_CARS":2}, {"STATE":"Alaska","TOTAL_CARS":5}]

Other pipelines examples:

Filter on Alaska state

{"$match":{"state":"Alaska"}}

Filter on items where state field is defined:

{"$match": {"state":{$exists :true}}}

Freemarker functions for collections

You can use Freemarker functions for collections in any freemarker script window in your processes.

Add an object

${save_object(object, collection_name)}

Example:

<#assign firstname = "Mike">
<#assign lastname = "Tyson">
<#assign phone_number = "">

<#assign my_object>
{"firstname":"${firstname}","lastname":"${lastname}","phone_number":"${phone_number}"}
</#assign>

${save_object(my_object,"client")}

It will return:

{"phone_number":"","_id":{"$oid":"4f516cc91eff614bfc239289"},"lastname":"Tyson","firstname":"Mike"}

Note : you'll notice a field _id. Do never perform requests based on this id.

Mass import objects from a csv file

${import_objects(file_id, collection_name [, separator [, drop]] )}

Import objects directly from a previously uploaded file with identifier file_id into collection named collection_name.

First line of file will be used as field name for the objects to be inserted :

firstname,lastname
John,Smith
Mike,Tyson

A separator may be specified using the separator parameter, the default separator is ,.

All objects will be inserted into the collection as a new object.

If an error occurs during the import, the operation is stopped, previously imported objects are kept in the collection.

If you want to drop the collection before the import, set the drop parameter to true.

The functions returns the number of imported objects.

Example:

${import_objects("6852c8c0-a694-11de-b93f-123138017842", "clients", ",", "true")}

List objects

${list_objects( query, collection_name [, first_index [,limit]])}

Load all objects matching the query (see Query Interface), in the collection collection_name.

To paginate the result, use first_index and limit parameters. The number of returned objects is limited to 100.

The functions returns a list of objects if some objects match the query, an empty list otherwise.

Example 1:

${list_objects({},"clients",10,25)}

25 objects starting at the index 10

Example 2:

<#assign firstname = "John">
<#assign my_pattern>
{"firstname":"${firstname}"}
</#assign>

${list_objects(my_pattern,"clients")}

retrieves all the clients with firstname = "John"

Result of list_objects function will be only available within your script : it can't be assigned directly to a process variable (see inject_objects function). You have to manipulate result to extract what you want to persist in your process.

Example:

<#assign tmp=[]>
<#list list_objects({},"clients") as x>
<#assign tmp=tmp+[x.lastname]>
</#list>
${tmp}

Load a single object

${load_object( query, collection_name)}

Same function as list_objects except that it returns only one result (even if several objects match the query) so result is a json structure and not an array of json structures

Count objects

${count_objects( query, collection_name)}

Count all objects matching the query (see Query Interface), in the collection collection_name.

It returns the number of objects matching the query.

Create collection

${create_collection(collection_name [, is_public [, is_readonly]])}

Create a new collection named collection-name in the current project.

If is_public is specified and is true, the collection will be publicly accessible.

If is_readonly is specified and is true only a read access will be granted to user in the User Access Right of the project, users with the Supervisor Access Right and Designer Access Right of the project will be able to read/write objects in this collection.

You can not use the same name for two collections, even if they are not in the same project.

The function returns true if the collection has been created, false otherwise.

Example:

${create_collection("clients","true","true")}

Drop a collection

${drop_collection(collection_name)}

Delete the objects of collection collection_name. All objects will be deleted, but the collection is still known at the project level.

The function returns trueif the collection has been dropped.

Delete a collection

${remove_collection(collection_name)}

Delete the collection collection_name. All objects will be deleted, the collection will be removed from the current project also.

The function returns true if the collection has been deleted.

Rename a collection

${rename_collection(collection_old_name, collection_new_name)}

Rename the collection collection-old-name to collection-new-name. You can not use the same name for two collections, even if they are not in the same project. The function returns true if the collection has been renamed, false otherwise.

Note : be very careful using this function. If you change the name of a collection, you will have to update all the processes/web interfaces that use this collection.

Update objects

${update_objects( query, object, collection_name [, multi])}

Update objects matching the query in the collection collection_name.

If multi is specified and is true all objects matching the query will be updated, otherwise only the first one will.

Be very careful: matching objects will be replaced by the specified document object. To modify only a field value, use the update_field function.

The function returns the number of updated objects.

Example:

${update_objects({"lastname":"Smith"},  {"lastname":"Smith","firstname":"John"},"clients","false")}

Update specific fields in objects

${update_field( query, field_object, collection_name [, multi])}

Update fields of objects matching the query in the collection collection_name. If multi is specified and is true all objects matching the query will be updated, otherwise only the first one will. field_object is a partial object, containing only the fields to be modified.

The function returns the number of updated objects.

Example:

${update_field( {"firstname":"John"}, {"lastname":"Smith"}, "clients", "true"   )}

Delete objects

${remove_objects( query, collection_name)}

Remove objects from the collection collection_name. The objects to be removed are specified using a query (see how to query a document)

The function returns the number of removed objects.

Example:

${remove_objects({},"clients")}

Remove a specific field in objects

${remove_field( query, field_name, collection_name [, multi])}

Update fields of objects matching the query in the collection collection_name. If multi is specified and is true all objects matching the query will be updated, otherwise only the first one will. The field named field_name will be removed from objects matching the query.

The function returns the number of updated objects.

Example:

${remove_field( {"firsname":"John"}, "phone_number", "clients", "true" )}

Inject objects in Freemarker variables

${inject_objects(objects [, field_name [,field_name] ...])}

Inject object values in the current computed parameters of the process. If no field-name are specified, all fields are injected, otherwise only the specified fields will be injected. objects may be a simple object, or an array of objects (the result of list_objectsor load_object).

The function returns detached objects from the collections, visible in the computed parameters of the data document.

Example :

<#assign tmp = list_objects({},"client")>
${inject_objects(tmp)}

Aggregation

In order to perform an aggregation on a collection, you need to use the following expression:

aggregate_collection( collection_name,my_pipeline1[,my_pipeline2[,my_pipeline3[,my_pipeline4...]]])

Let's use this collection for our example :

Where fields for every item are [first_name,last_name,state,nb_of_cars,nb_of_motors]

Let's say we want to display the number of cars per state for the people having 2 as number of motors. Therefore, we have to build our aggregate expression and use a $match pipeline then a $group pipeline. The grouping key should be the state and is specified using a _id object. We will use the $sum function to calculate the total number of cars per state.

<#assign my_pipeline1>
 {"$match":{"nb_of_motors":2}}
</#assign>
<#assign my_pipeline2>
 {"$group":{"_id":{"State":"$state"},"Total":{"$sum":"$nb_of_cars"}}}
</#assign>

$group,_id,$sum are reserved words. $state,$nb_of_cars are the names of the fields in the collection.

Now we have to define which fields we want to project and show in the final result. Therefore, we have to use the $project pipeline:

<#assign my_pipeline3>
 {"$project":{"STATE":"$_id.State","TOTAL_CARS":"$Total"}}
</#assign>

As shown above, State must be prefixed by $_id for it's part of the grouping key.

We can also sort the result using a $sort pipeline:

<#assign my_pipeline4>
 {"$sort":{"STATE":1}}
</#assign>

The final code will be :

<#assign my_pipeline1>
 {"$match":{"nb_of_motors":2}}
</#assign>
<#assign my_pipeline2>
 {"$group":{"_id":{"State":"$state"},"Total":{"$sum":"$nb_of_cars"}}}
</#assign>
<#assign my_pipeline3>
 {"$project":{"STATE":"$_id.State","TOTAL_CARS":"$Total"}}
</#assign>
<#assign my_pipeline4>
 {"$sort":{"STATE":1}}
</#assign>

<#assign my_pipeline1 = my_pipeline1?eval>
<#assign my_pipeline2 = my_pipeline2?eval>
<#assign my_pipeline3 = my_pipeline3?eval>
<#assign my_pipeline4 = my_pipeline4?eval>

<#assign res=aggregate_collection("my_col",my_pipeline1,my_pipeline2,my_pipeline3,my_pipeline4)>
${res}

Advanced queries

Examples are in javascript but the pattern remains the same for other languages.

Filter using regular expression

pattern = {"field":{"$regex" : "YOUR_REGEXP" , "$options" : "i" }};

Note : the i option means it's case insensitive.

Example:

var my_pattern = {};
my_pattern.destination_label = {"$regex" : ".*gambetta.*" , "$options" : "i" };
id_collection.listCallback(my_pattern,{},callbackSuccess,callbackFailure);

This will retrieve all the elements whose destination_label field contains "gambetta" (case insensitive)

Filters on arrays

You have a list of items like this:

[{
"details" : {
"country" : ["FR", "UK", "USA"],
"site" : "ABC"
},
"details" : {
"country" : ["FR", "ES", "IT"],
"site" : "ABC"
},
"details" : {
"country" : ["JP"],
"site" : "ABC"
}
}
]

If you want to get all the items whose country array contains "FR" then your pattern will be:

{"details.country":"FR"}

If you want to get all of the items whose country array contains "FR" and not "UK", your pattern will be:

{"$and":[{"details.country":"FR"},{"details.country":{"$nin":["UK"]}}]}

Sort items in a report

In a widget "Report of a collection", you can sort items.

In the query field you can enter {"$query":YOUR PATTERN,"$orderby":{"FIELD TO SORT":1 FOR ASCENDING / -1 FOR DESCENDING}}

Example:

{"$query":{"year":"2013"},"$orderby":{"service":-1}}

This will retrieve all the items matching year=2013 ORDER BY service descending

Aggregation in a report

In a report widget over collection, there are two ways to apply aggregation:

1- Directly in the report configuration in the web interface

2- Using JS.

var my_pipelines = [{
"$group" : {
"_id" : {
"State" : "$state"
},
"Total" : {
"$sum" : "$nb_of_cars"
}
}
}, {
"$project" : {
"STATE" : "$_id.State",
"TOTAL_CARS" : "$Total"
}
}
];

id_report.setAggregate(JSON.stringify(my_pipelines));
id_report.refresh();

In both cases, all fields in the $project pipeline must be configured in the report columns definition :

Aggregated report will look like:

Best practices

1. When you create new objects in the collection, use a specific field as unique identifier.
   E.g: you want to add a new client in your collection.
   You object will have to look like : {"firstname:"John","lastname":"Smith","client_id":"CUST3456"}
   This client_id field will allow you to retrieve this specific object in the future. If you build variable based list from collection objects, it's mandatory you use different and unique values.

2. When you choose collection names and identifiers, use only lower case characters and underscores "_"