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
returned by explicitly selecting or excluding fields.
This topic includes examples 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.
filter
where <value>
limit <value>
skip <value>
order (1..n)
<fieldname>/<value>
field (1..n)
<fieldname>/<value>
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
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"and": [{"price": {"gte":325}},
{"phone": {"regexp": "^08"}}]}';
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 using 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.
- When you use a model in conjunction with a filter and
a field clause that explicitly selects the set of
fields to be returned, some connectors return a
null
value for all field values that are not explicitly selected or excluded.
When using a Mapping node to achieve this, you can create the required fields by adding a user-defined element. For more information, see Adding a user-defined element.
SET OutputLocalEnvironment.Destination.Loopback.Request.id = '500 ';
SET OutputLocalEnvironment.Destination.Loopback.Request.externalId = '100 ';
SET OutputLocalEnvironment.Destination.Loopback.Request.externalIdName = 'companyId';
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"companyId":"400 "}';
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;
SET OutputLocalEnvironment.Destination.Loopback.Request.filter.where = '{"and": [{"totalEmployees": {"lte":430}},
{"companyName": {"like": "B%"}}]}';
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}';
SET OutputLocalEnvironment.Destination.Loopback.Request.filterString = '{"where": {"and": [{"totalEmployees": {"lte":430}},
{"companyName": {"nlike": "B%"}}]}, "order": ["companyName ASC", "totalEmployees DESC"], "fields": {"companyName": true,
"totalEmployees": false}}';