Stored Queries

Stored Queries are MongoDB🌳 queries optimized for frequent or automated use. They enable direct interaction with the MongoDB database, where tenant-specific information is stored. This mechanism is particularly useful for improving application performance by reducing latency associated with the execution of repeated queries.

In this documentation, we won't dwell on the general syntax of MongoDB, but will focus on the MongoDB stages, which Activator has implemented to date. These stages enable data to be processed, filtered and manipulated efficiently to meet the needs of end users.

Note⛔:

Intermediate-level knowledge of MongoDB is recommended to work effectively with this component. This article aims to introduce developers to the creation of MongoDB queries in the Activator environment.

How To Create A Stored Queries

In Activator Admin, Sored queries are located within the menu container, As shown at (1) on the image below.

✋NB:

Make sure you are in the System Components main module.

After initiating the creation of the Role, by clicking on the +Add new button (illustrated at (2) on the image above), you’ll observe a form (illustrated at (3) on the image above) appearing on the right side with some fields

🔬After filling in the name and description fields, let's focus on the Query Type and Target.

Query Type and Target.

The Query Type field brings together three types of Storedquery:

• SelectOne: This type of stored query does not implement MongoDB's lookup stage and returns only the first element found. When the $project  field is defined as an empty object ({}), all record properties will be returned. No join stage (lookup) is performed.
• SelectMany: This type of stored query does not implement MongoDB's lookup stage and returns the list of elements found. When the $project  field is defined as an empty object ({}), all record properties will be returned.
• Aggregation: Returns the list of elements found. In this case, the properties of the record to be returned must be specified in the $project field. This type of stored query implements MongoDB's stage lookup.

The Target field is used to link it to an entity.

How To Send Parameters To A Stored Query

After creating a stored query, you can go to the additional info section to pass parameters.

The parameter property, which is used to send parameters to the stored query. Each parameter is represented by an object containing several attributes, such as described below:
For example, for a storedQuery named @parameters.parameterName

"parameters": [
    {
        "name": "recordId",
        "required": true,
        "dataType": "Guid"
    }
]

// The recordID will be called by “@parameters.recordId”.

Source

At the root Activator implements three stages:

• $match
• $project
• $limit

Execute A StoredQuey

By API

{
    "parameters": [
        {
            "name": "string",
            "value": "string"
        }
    ]
}

By storedFunction

With no parameter

string resultJson = await this.Context.StoredQueries.Execute("activatord.storedqueries.newStoredQuery1");

With Parameter

var param1 = new QueryParameter
{
    Name = "param1",
    Value = "Value1"
};

var param2 = new QueryParameter
{
    Name = "param2",
    Value = "Value2"
};

string resultJson = await this.Context.StoredQueries.Execute(
"activatord.storedqueries.newStoredQuery1",
new List<QueryParameter> { param1, param2
}
);

MongoDB Stages Supported By Activator

$match

Match With Params

{
    "$match": {
        "_id": "@parameters.recordId",
        "createdDate": {
            "$gte": "~~ISODate('@parameters.startDate')~~",
            "$lte": "~~ISODate('@parameters.endDate')~~"
        }
    }
}

✋Note:

In this example, the recordId parameter must be added to the storedquery's addtionalInfo section.

As for the startDate and endDate parameters, they will first be converted and then considered as an interval.

Match With No Params

{
    "$match": {
        "entityComponent.name": "activatord.businessprocesses.newOpportunityProcess",
        "isActive": true,
        "name": "John Doe",
        "effectiveDate": {
            "$nin": [
                null,
                ""
            ]
        }
    }
}

$project

MongoDB's $project stage is used to include, exclude or transform specific fields in documents returned by a query. It is used in aggregation pipelines to reformat documents by selecting only the desired fields, rename fields or perform operations on field values.

Example:

"$project": {
    "_id": 1,
    "recordId": "$_id",
    "name": 1,
    "age": {
        "$toString": "$age"
    },
    "city": "$localization.city",
    "currentBalance": {
        "$toString": "$balance.value"
    },
    "birthPlace": "$origin.city",
    "dateOfBirth": {
        "$dateToString": {
            "format": "%m/%d/%Y",
            "date": "$dateOfBirth"
        }
    }
}

✋Note:

• 1 and 0 are used to include (1) or exclude (0) fields in the result.
• Field renaming allows you to reorganize or customize field names in the returned document, using the syntax newName: $oldName.
• Converters such as $toString can be used to change the data type of a field (e.g. convert an integer to a string).
• Date formatters like dateToString allow you to format dates according to specific patterns in the results.

$lookup

MongoDB's $lookup stage is used to perform a join between two collections, similar to a join in relational databases (such as SQL). It allows you to combine documents from two different collections into a single result, by linking documents based on a common field.

"$lookup": {
    "from": "activatord.entities.nameEntity",
    "localField": "member.recordId",
    "foreignField": "_id",
    "as": "memberRecord"
}

$unwind

MongoDB's $unwind stage is used to decompose an array into multiple documents, each containing a single element of the array. This makes it possible to transform documents containing tables into individual documents, facilitating data aggregation and analysis operations.

"$unwind": {
    "path": "$memberRecord",
    "preserveNullAndEmptyArrays": true
}

$group

MongoDB's $group stage is used to perform aggregations on sets of documents, grouping documents by one or more fields and calculating aggregate values (such as sums, averages, counts, etc.) for each group. This enables data to be efficiently summarized and analyzed.

"$group": {
    "_id": "$relation",
    "count": {
        "$sum": 1
    }
}

$limit

MongoDB's $limit stage is used to restrict the number of documents returned by a query or aggregation pipeline. It allows you to control the amount of data returned, which is particularly useful for improving performance and reducing load on the network or client.

"$limit": 500

$sort

MongoDB's $sort stage is used to sort documents in a collection according to one or more fields. This allows you to specify the order in which results should be returned, either ascending or descending.

"$sort": {
    "createdDate": -1
}

Conclusion

This article illustrates the use of stored queries with MongoDB on the Activator platform. We've covered the basics of creating and managing MongoDB queries in this context, offering an overview of the features and possibilities they offer. However, it's important to note that this introduction is just a starting point. To learn more about implementation details and best practices, we strongly recommend that you consult the official MongoDB documentation.