Filtering the records retrieved through a LoopBack connector

Use the filter element in the LocalEnvironment.Destination.Loopback.Request.filter environment variable to restrict the content of records that are retrieved from the LoopBack® application.

About this task

You can restrict the content of records that are returned by using the LocalEnvironment.Destination.Loopback.Request.filter environment variable to specify a filter with a where, limit, or skip clause. You can also specify a filter with an order clause, and the records are returned in the specified order. The returned records appear as a JSON array or a JSON object in the location that is specified by the Output data location property of the LoopBackRequest node. By default, records are returned with all fields populated; however, you can use a filter with a field clause to restrict the data that is returned by explicitly selecting or excluding fields.

Examples are shown of how to set the values by using ESQL; however, you can also set them by using a transformation node such as a Mapping node. To access the LocalEnvironment from a Mapping node, see Customizing a message map to include a message assembly component.

By using the LocalEnvironment.Destination.Loopback.Request.filter environment variable, you can specify filter clauses, by using child elements of the filter element:

filter
     where <value>
     limit <value>
     skip <value>
     order (1..n)
          <fieldname>/<value>
     field (1..n)
          <fieldname>/<value>

You can specify the child elements of the filter element to restrict the content of the records that are retrieved. The available elements are shown in the following table:

Table 1. Elements used for filtering the content of records retrieved from a backend data source
Element	Type	Description
where	string	A condition expression used to filter the set of returned records.
limit	positive integer	Imposes a maximum number of records in the returned set.
skip	positive integer	Removes the first `n` records from the returned set before returning the number of results up to the specified limit.
order	structure	One child, the name of which must match the name of a field of the record. The value is either `ASC` or `DESC` (case-sensitive) and is used to order the returned set. You can specify multiple order clauses, which are applied in the order in which they appear in the filter tree.
field	structure	One child, the name of which must match the name of a field of the record. The value can be either `true` or `false` (or `1` or `0`), and controls whether the field is included as part of each returned record. You can specify multiple field clauses.

Examples

The following examples show some of the query filter options that you can use with the LoopBackRequest node.

The filtering options that are available to you depend on the capabilities of the specific LoopBack connector that you are using. The precise syntax and outcome of the filter also depend on the type and version of the installed LoopBack connector.

Get documents from MongoDB, where price is >= 325 and phone begins with 08:

SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"and": [{"price": {"gte":325}},
 {"phone": {"regexp": "^08"}}]}';

Get all records in descending totalEmployees order and ascending companyName order, retrieving only the companyName field, and explicitly excluding the totalEmployees field:

SET OutputLocalEnvironment.Destination.Loopback.Request.filter.order[1].totalEmployees = 'DESC';
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.order[2].companyName = 'ASC';
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.field[1].companyName = true;
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.field[2].totalEmployees = false;

When you use a field clause that explicitly selects the set of fields to be returned, some connectors continue to return the id field unless it is explicitly excluded.
You can use a model along with a filter and a field clause that explicitly selects the set of fields to be returned. When you use this option, some connectors return a null value for all field values that are not explicitly selected or excluded.

When you use a Mapping node to achieve this task, you can create the required fields by adding a user-defined element. For more information, see Adding a user-defined element.

When you use an ESQL Compute node to achieve this task, you must set the Compute mode to include LocalEnvironment.

Query by id, where the model defines the identity field as companyId. In this case, the companyId value is space-padded to match the length of a fixed-length character column in the backend database:

SET OutputLocalEnvironment.Destination.Loopback.Request.id = '500  ';

Query by externalId and externalIdName by using companyId as the value for externalIdName. In this case, the companyId value is space-padded to match the length of a fixed-length character column in the backend database:

SET OutputLocalEnvironment.Destination.Loopback.Request.externalId = '100  ';			
SET OutputLocalEnvironment.Destination.Loopback.Request.externalIdName = 'companyId';

Query using companyId in a filter.where clause. In this case, the companyId value is space-padded to match the length of a fixed-length character column in the backend database:

SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"companyId":"400  "}';

Query using totalEmployees >= 325 in a filter.where clause. Get two records and skip over the first one:

SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"totalEmployees": {"gte":325}}';
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.limit = 2;		
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.skip = 1;

Query that uses totalEmployees <= 430 and companyName begins with B:

SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"and": [{"totalEmployees": {"lte":430}},
 {"companyName": {"like": "B%"}}]}';

Query that uses filterString, where totalEmployees is <= 430 and companyName does not begin with B. Get only one record (limit=1) and ignore the first one (skip=1):

SET OutputLocalEnvironment.Destination.Loopback.Request.filterString = '{"where": {"and": [{"totalEmployees": 
 {"lte":430}}, {"companyName": {"nlike": "B%"}}]}, "limit":1, "skip":1}';

Query that uses filterString where totalEmployees is <= 430 and companyName does not begin with B. Get the records in ascending companyName order and then descending totalEmployees order. Request the companyName field and explicitly exclude the totalEmployees field:

SET OutputLocalEnvironment.Destination.Loopback.Request.filterString = '{"where": {"and": [{"totalEmployees": {"lte":430}},
 {"companyName": {"nlike": "B%"}}]}, "order": ["companyName ASC", "totalEmployees DESC"], "fields": {"companyName": true,
 "totalEmployees": false}}';